Query Between Dates, or Null

[kagoodwin13]

I'm looking for the syntax on how to do a query I've done many times, but I've lost the source code.

Process for command:
1. Select Start Date on Form
2. Select End Date on Form
3. Press "Run Query" and query selects based on criteria between dates
4. If Start Date is empty, query selects based on less than End Date
5. If End Date is empty, query selects based on greater than Start Date
6. If Start Date and End Date are empty, query selects all results

Does anyone have this query handy?



Thanks in advance!

[June7]

Maybe like:

SELECT * FROM tablename WHERE [date field] BETWEEN Nz(Forms!formname![Start Date], #1/1/1900#) AND Nz(Forms!formname![End Date], #12/31/2999#);

[kagoodwin13]

Yes, that's it! Thank you June.

Between Nz([Forms]![frmSEARCH]![txtStart_Date],#1/1/1900#) And Nz([Forms]![frmSEARCH]![txtEnd_Date],#9/9/9999#)