How to ignore parameter?

[pablavo]

Hi there

I've been looking for this one for a while. In my query I'm using a date field to extract dates using the between And function. I'm refrencing to txt boxes within a dialog form within the query.

For example:
Between [Forms]![frmViewByCatagory]![TxtStartDate] And [Forms]![frmViewByCatagory]![txtEndDate]

The field actually only lists years rather whole dates .
I want to find a way to add an expression so that I can leave the txt boxes blank and access will ingnore the txt boxes and give me all the records.

Does anyone have an idea what I can do here. I thought the IIF function along with Null might work but I don't know the syntax.

Thanks

 

[Kinger43]

Criteria statement

Put this in the criteria in place of the between...and... statement

(Between [Forms]![formname]![startdate] And [Forms]![formname]![endate]) Or ([Forms]![formname]![startdate] Is Null And [Forms]![formname]![endate] Is Null)

If you leave the fields blank then it will return all the records. If you enter dates it will return everything between those dates.

 

[pablavo]

Thanks very much Kinger43! that worked perfectly.

 

[Kinger43]

glad it worked for you.