If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Between Dates or All Dates if No Date Entered
All,
I can't find info on how to do this in Access Help, and am hoping it can be done... I have a query with the following in the Criteria field for my Export Date: =#5/1/2009# And Between [Enter a Starting Export Date] And [Enter an Ending Export Date] How do I enhance this Criteria so that If the user does not enter a Starting Export Date, the query retrieves all records back to 5/1/2009. If the user does not enter an Ending Export Date, the query retrieves all records forward to the latest record after 5/1/2009. If the user enters neither a Starting Export Date or an Ending Export Date, the query retrieves all records from 5/1/2009 to the latest record. I've tried using "*", but did find info that that will not work with Between. Thanks! Patrick |
#2
|
|||
|
|||
Between Dates or All Dates if No Date Entered
IF the field always has a value - no nulls or if you don't care about null
values then you can use something like the following. BETWEEN NZ([Enter a Starting Export Date],#1/1/1900#) A nd NZ([Enter an Ending Export Date],#12/31/3000#) If no start date is entered then all records dated after Jan 1, 1900 to the End date will be returned. If no end date is entered then all records between the start date and Dec 31, 3000 will be returned If both are left blank then all records between Jan 1, 1900 and Dec 31, 3000 will be returned. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County PatrickA wrote: All, I can't find info on how to do this in Access Help, and am hoping it can be done... I have a query with the following in the Criteria field for my Export Date: =#5/1/2009# And Between [Enter a Starting Export Date] And [Enter an Ending Export Date] How do I enhance this Criteria so that If the user does not enter a Starting Export Date, the query retrieves all records back to 5/1/2009. If the user does not enter an Ending Export Date, the query retrieves all records forward to the latest record after 5/1/2009. If the user enters neither a Starting Export Date or an Ending Export Date, the query retrieves all records from 5/1/2009 to the latest record. I've tried using "*", but did find info that that will not work with Between. Thanks! Patrick |
#3
|
|||
|
|||
Between Dates or All Dates if No Date Entered
Alternatively:
PARAMETERS [Enter a Starting Export Date] DATETIME, [Enter an Ending Export Date] DATETIME; SELECT * FROM YourTable WHERE ([Export Date] = [Enter a Starting Export Date] OR [Enter a Starting Export Date] IS NULL) AND ([Export Date] DATEADD("d",1,[Enter an Ending Export Date]) OR [Enter an Ending Export Date] IS NULL); Two points about this: 1. Its best to always declare date/time parameters as they might otherwise be misinterpreted as arithmetic operations and give the wrong results. You can do this in SQL view as above or in query design view. 2. Defining the date range in the above way is more bullet-proof than a BETWEEN….AND operation as it caters for any date/time values in the column which might inadvertently have a non-zero time of day element. Unless you have taken specific steps in the table definition to disallow such values you cannot guarantee that none are present in the column. Any such values on the final day of the range would not be returned by a BETWEEN….AND operation, whereas defining the range by dates on or later than the start date and before the day following the end date is guaranteed to pick them up. Ken Sheridan Stafford, England On May 6, 3:58 pm, PatrickA wrote: All, I can't find info on how to do this in Access Help, and am hoping it can be done... I have a query with the following in the Criteria field for my Export Date: =#5/1/2009# And Between [Enter a Starting Export Date] And [Enter an Ending Export Date] How do I enhance this Criteria so that If the user does not enter a Starting Export Date, the query retrieves all records back to 5/1/2009. If the user does not enter an Ending Export Date, the query retrieves all records forward to the latest record after 5/1/2009. If the user enters neither a Starting Export Date or an Ending Export Date, the query retrieves all records from 5/1/2009 to the latest record. I've tried using "*", but did find info that that will not work with Between. Thanks! Patrick |
#4
|
|||
|
|||
Between Dates or All Dates if No Date Entered
Thanks Ken!
Thanks John! |
Thread Tools | |
Display Modes | |
|
|