date range parameter query ?

[mitchem1]

My parameter query using Between [StartDate] and [EndDate] works well. How would one set it up so that if the user enters nothing in the StartDate and EndDate fields, all records are returned? Thanks.

 

[Hayley Baxter]

Enter underneath what you have in the OR section of the query

Like *

HTH

Hay

 

[Jon K]

Try this setting in a column in the query grid (using the correct date field name):-

Field: IIf(IsNull([StartDate]) Or IsNull([EndDate]), [DateFieldName]=[DateFieldName] Or IsNull([DateFieldName]), [DateFieldName] Between [StartDate] And [EndDate])

Show: uncheck
Criteria: <>False

 

[mitchem1]

Hey Jon,
Thanks for the reply. That looks like it might work for me. Not sure I understand what the Criteria setting <>False does.

 

[Jon K]

The <>False in the Criteria cell is one of Access's defaults. It tells Access the IIf() expression in the Field cell is to be treated as a Criteria, i.e. to be included in the Where clause of the SQL statement.

If you directly type the IIf() expression in the Where clause of the SQL statement in the SQL View of the query, you don't need to type <>False.