Query Date Criteria

[gheyman]

Can you use an IIF statement in the query?

I want to be able to say

if a Check Box = Yes than show all,
If the Start and End Text Box where the criteria is looking for the date range are null = then show all
If the Start TextBox where the criteria is null show all that are earlier than whats in the End Textbox up to the ate in the End Textbox
If the Start Date is not null and the End Date is null (or blank) the show all from the start date and later.

Yes that's a mouth full!

Thanks

 

[xenou]

This is my attempt, using a query and a form with two unbound text boxes:

SELECT *
FROM GlucoseReadings
WHERE (((GlucoseReadings.[RecordDate])>=DateValue(Nz([Forms]![Form1].[txtStartDate].[Value],"1/1/1970")) And (GlucoseReadings.[RecordDate])<=DateValue(Nz([Forms]![Form1].[txtEndDate].[Value],"12/31/2099"))));

Using the date value function because the values in the textboxes are text, not values with date datatypes (and ignoring the danger of invalid entries in a freeform text box, for now).

Basically, if the dates are missing/null/blank/empty then we are just substituting a minimum date that represents the beginning of time for some relevant period, and likewise for the maximum date.