Results 1 to 11 of 11
  1. #1
    Pam@NZ is offline Novice
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    6

    Using between dates criteria from form to query?

    Hi there and thanks in advance for any help,

    I have a simple unbound form with two text fields in which two dates are entered: Date From and Date To.
    It has a button that opens a query. The resulting data has a date field.

    I have this in the query criteria in the date field... >=[forms]![frm_Extract_Informal_Events]![txt_DateFrom] And <=[Forms]![frm_Extract_Informal_Events]![txt_DateTo]

    This works well if both the from and to dates are entered into the form, but returns zero results if just one date or neither dates are entered.

    What I want is that if one or both of the form date fields is left blank then it affects the query as follows:


    If only From Date then the results have all records from that date to now.
    If only To Date then the results have all records up to that date.
    If none are entered then return all results.

    I have tried having two fields in my query - one each for the from and to dates, but it still doesn't work. I'm assuming none of this works because my criteria contains operators (=>), and if a date is left blank then the operators remain in the criteria.

    I want to keep the query as an object rather than as SQL.

    Has anyone any ideas how I can do this? The only solution I can see is having 4 queries and calling the appropriate one depending on what is in the form. Yuck!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try something like:

    >=Nz([forms]![frm_Extract_Informal_Events]![txt_DateFrom], #1/1/2000#) And <=Nz([Forms]![frm_Extract_Informal_Events]![txt_DateTo], #12/31/2039#)

    Basically using dates that are before/after anything in your data.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.

    I would use the Me.Filter and Me.FilterOn properties to filter the form.
    Another method is to use VBA to create the WHERE clause or the query, then save the SQL to saved query. If you were using a form,
    Since you want to open a saved query(?), then modifying the query SQL is the way to go.

    Example:
    MS Access VBA – Edit a Query’s SQL Statement

    You will have to write code to determine which condition
    If only From Date then the results have all records from that date to now.
    If only To Date then the results have all records up to that date.
    If none are entered then return all results.
    to have the WHERE clause use (use a Case statement)..



    I'll try and write some code later tonight....

  4. #4
    Pam@NZ is offline Novice
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    6
    Brilliant! - that worked

    I was calling via ODBC which made it very slow so needed to create a tmp table with all recs first and then extract from this using the criteria string you suggest - but that's not a biggy.

    thanks heaps!

  5. #5
    Pam@NZ is offline Novice
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    6
    Quote Originally Posted by pbaldy View Post
    Try something like:

    >=Nz([forms]![frm_Extract_Informal_Events]![txt_DateFrom], #1/1/2000#) And <=Nz([Forms]![frm_Extract_Informal_Events]![txt_DateTo], #12/31/2039#)

    Basically using dates that are before/after anything in your data.
    Brilliant! - that worked

    I was calling via ODBC which made it very slow so needed to create a tmp table with all recs first and then extract from this using the criteria string you suggest - but that's not a biggy.

    thanks heaps!

  6. #6
    Pam@NZ is offline Novice
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    6
    Quote Originally Posted by ssanfu View Post
    Welcome to the forum.

    I would use the Me.Filter and Me.FilterOn properties to filter the form.
    Another method is to use VBA to create the WHERE clause or the query, then save the SQL to saved query. If you were using a form,
    Since you want to open a saved query(?), then modifying the query SQL is the way to go.

    Example:
    MS Access VBA – Edit a Query’s SQL Statement

    You will have to write code to determine which condition

    to have the WHERE clause use (use a Case statement)..



    I'll try and write some code later tonight....
    Thanks for the suggestion and offer of providing code... but the form is unbound - only for entering criteria for the query. I am not displaying the results of the query on a form.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Pam@NZ View Post
    Brilliant! - that worked

    I was calling via ODBC which made it very slow so needed to create a tmp table with all recs first and then extract from this using the criteria string you suggest - but that's not a biggy.

    thanks heaps!
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By the way, if it's slow you might consider a pass through query to the back end if that's an option.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Pam@NZ is offline Novice
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    6
    Quote Originally Posted by ssanfu View Post
    Welcome to the forum.

    I would use the Me.Filter and Me.FilterOn properties to filter the form.
    Another method is to use VBA to create the WHERE clause or the query, then save the SQL to saved query. If you were using a form,
    Since you want to open a saved query(?), then modifying the query SQL is the way to go.

    Example:
    MS Access VBA – Edit a Query’s SQL Statement

    You will have to write code to determine which condition

    to have the WHERE clause use (use a Case statement)..



    I'll try and write some code later tonight....
    Thanks for the suggestion and offer of providing code... but the form is unbound - only for entering criteria for the query. I am not displaying the results of the query on a form.

  10. #10
    Pam@NZ is offline Novice
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    6
    Quote Originally Posted by pbaldy View Post
    By the way, if it's slow you might consider a pass through query to the back end if that's an option.
    Thanks - but no, returning call error - I imagine the database owner is not allowing that. (caveat that's the first time I've created and run a pass-through so I'm assuming my reasoning is ok)

  11. #11
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yes, Paul's solution is better.
    I tend to use "brute force" and use VBA to get things done.

    Glad you got it working.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. IIfs and Dates in a query criteria
    By MykelDL in forum Queries
    Replies: 5
    Last Post: 05-08-2017, 05:26 PM
  2. Query with many criteria (Dates, Condition, etc)
    By soldat452002 in forum Queries
    Replies: 5
    Last Post: 06-25-2014, 05:01 PM
  3. Using Dates in Query Criteria
    By brett621 in forum Queries
    Replies: 12
    Last Post: 07-02-2013, 11:27 AM
  4. Dates in query criteria
    By thart21 in forum Queries
    Replies: 7
    Last Post: 01-24-2011, 11:56 AM
  5. Dates: query and criteria
    By isnpms in forum Queries
    Replies: 5
    Last Post: 08-22-2010, 08:01 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums