Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: Date Range Syntax

    Using 'from' and 'to' dates that I'm capturing via GUI/form and passing to a table, I'd like to imbed these date parameters in the criteria of a select query. Normally this would simply be stated Between [Data_Table]![Post_Date_From] And [Data_Table]![Post_Date_To].

    I however, need to add some conditional logic which will allow for a user to leave the form date parameters empty/null. For example:

    Between Like IIf([Data_Table]![Post_Date_From] IsNull,"*",[Data_Table]![Post_Date_From])
    And Like [IIf([Data_Table]![Post_Date_To] IsNull,"*",[Data_Table]![Post_Date_To])

    The result: If the user leaves the 'from' and 'to' date(s) blank, the select query returns all records, regardless of the date. If the user specifies one or both dates, then the select query uses those parameters.

    I can't seem to get the correct syntax? I've tried placing '{}' ,'[]', and '()' after Between and following And, but this doesn't work.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would build your string dynamically at the cgi/form level. Also, "LIKE" is a string comparison, you want to do date comparisons...

    You could use a set of OR statements if you want to go that route:

    WHERE (yourDate BETWEEN Post_Date_From AND Post_Date_To) OR (yourDate > Post_Date_From AND Post_DAte_To IS NULL) OR (yourDate < Post_Date_To AND Post_Date_From IS NULL)

    I'm making some assumptions that Post_Date_From and Post_Date_To are values stored in some kind of staging "parameter" table...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2006
    Posts
    163
    Instead of using a wildcard why not use dates that will cover the whole range.

    Between Nz([Data_Table]![Post_Date_From],#01/01/1900#) And Nz([Data_Table]![Post_Date_To],#31/12/2050#)

  4. #4
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Thank you both. You're right, I was mixing a string comparison (Like) with dates. The logic works except for one small problem I just discovered......

    The data field I'm querying against, formated as Date/Time, can also contain null values.

    So, if the user populates the form field with a "From" date, it's no problem. However, if they leave it null, then I need to return all records, which would include dates > say 01/01/1900 plus the records with null date values. What a mess!

  5. #5
    Join Date
    Mar 2006
    Posts
    163
    How are you actually opening the query?

  6. #6
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    If you're referring to my select query which contains the criteria/date parameters input by the user on a form, it's actually the data source for a report. It's not opened directly.

  7. #7
    Join Date
    Mar 2006
    Posts
    163
    So are you opening the query using code?

    What I was thinking was that rather than using parameters you could use a bit of code to set the date range for the report.

    That is use the WhereCondition argument of DoCmd.OpenReport.

  8. #8
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    I'm using DoCmd.OpenReport, but also outputing the results of the select query to Excel if the user elects that option.

    My criteria in the expression builder:

    Between [Selection Criteria]![Post Date (From)] And [Selection Criteria]![Post Date (To)]

    is actually working when Forms![Selection Criteria]![post_date_from] Is Null because I'm setting [Selection Criteria]![Post Date (From)] = #01/01/1900# on my staging table and returning all Post Dates since the beginning of time.

    I just need to also return records with a Null Post Date.

    If the user specifies a post_date_from, I'm then excluding any records with a Null Post Date.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •