If dates are null then return all records (1 Viewer)

[james_IT]

Hi - i have a query with the following code which searches the 'BookingDate' field for records between two dates:

Between [Forms]![FilteringForm]![txtStartDate] And [Forms]![FilteringForm]![txtEndDate]

this works fine however if the user doesnt put any dates in either box how can i return all records?

Also - if the user just enters a start date but not an end date how can i return records from the start date until now?

Thanks in advance

 

[pr2-eugin]

Well a very cheeky way of doing would be using Nz function. as follows.

Between Nz([Forms]![FilteringForm]![txtStartDate],#1/1/1989#) And Nz([Forms]![FilteringForm]![txtEndDate],Date())

Considering the fact that there will not be records older than 1/1/1989

 

[james_IT]

Well a very cheeky way of doing would be using Nz function. as follows.

Between Nz([Forms]![FilteringForm]![txtStartDate],#1/1/1989#) And Nz([Forms]![FilteringForm]![txtEndDate],Date())

Considering the fact that there will not be records older than 1/1/1989

you're a star!! Thank you so much

 

[pr2-eugin]

You are most welcome.

 

[MSAccessRookie]

Hi - i have a query with the following code which searches the 'BookingDate' field for records between two dates:

Between [Forms]![FilteringForm]![txtStartDate] And [Forms]![FilteringForm]![txtEndDate]

this works fine however if the user doesnt put any dates in either box how can i return all records?

Also - if the user just enters a start date but not an end date how can i return records from the start date until now?

Thanks in advance

I think that the Domain Function DMin() could get your problem resolved. Give it a try and come back with any additional questions.

-- Rookie

NOTE:

I noticed that while I was posting, pr2_eugin posted an alternate, and very simple solution. The only shortcoming to that method (defining the Mimimum Date) was outlined in advance. The DMin() Function will eliminate that issue by using the Minimum data available in the existing Dataset.

 

[james_IT]

I think that the Domain Function DMin() could get your problem resolved. Give it a try and come back with any additional questions.

-- Rookie

NOTE:

I noticed that while I was posting, pr2_eugin posted an alternate, and very simple solution. The only shortcoming to that method (defining the Mimimum Date) was outlined in advance. The DMin() Function will eliminate that issue by using the Minimum data available in the existing Dataset.

thanks very much - the db will not include historic data - only data from go-live onwards so i dont think ill have an issue. thanks anyway

 

[james_IT]

You are most welcome.

hiya - just input some more test data.

If date fields are left blank, it only displays records up to today's date?? I.e. it doesn't show ALL records, past and future?

 

[pr2-eugin]

James, the ones I gave you are limited from 1 Jan 1989 till Today. But if you want to include all data (past beyond 1 Jan 1989 and Future 21 Dec 2012), you can either use predefined dates as above or use DMin and DMax as suggested by Rookie. As it looks into you current Dataset for Minimum value and Maximum values. MAkes sense??

 

[james_IT]

James, the ones I gave you are limited from 1 Jan 1989 till Today. But if you want to include all data (past beyond 1 Jan 1989 and Future 21 Dec 2012), you can either use predefined dates as above or use DMin and DMax as suggested by Rookie. As it looks into you current Dataset for Minimum value and Maximum values. MAkes sense??

thanks - trying

Between ([Forms]![FilteringForm]![txtStartDate]) And ([Forms]![FilteringForm]![txtEndDate]) Or Between DMin("BookingDate","Bookings") And DMax("bookingdate","bookings")

but i've gone wrong...?

 

[pr2-eugin]

Yes you have. Not to worry.. Since you want to use the minimum and maximum value in the table. 'ONLY' when you have not entered values in the Form it follows the same Nz but instead of the default date values, we will be using the DMin and DMax in their place.. Something like..

Between 
Nz([Forms]![FilteringForm]![txtStartDate],DMin("[BookingDate]","[Bookings]")) 
And 
Nz([Forms]![FilteringForm]![txtEndDate],DMax("[BookingDate]","[Bookings]"))

 

[james_IT]

Yes you have. Not to worry.. Since you want to use the minimum and maximum value in the table. 'ONLY' when you have not entered values in the Form it follows the same Nz but instead of the default date values, we will be using the DMin and DMax in their place.. Something like..

Between 
Nz([Forms]![FilteringForm]![txtStartDate],DMin("[BookingDate]","[Bookings]")) 
And 
Nz([Forms]![FilteringForm]![txtEndDate],DMax("[BookingDate]","[Bookings]"))

once again...you sir are THE MAN. thank you and have a nice weekend