requetin a particular date vs all dates

[billheath]

I have a query with a [Date_New] field. When I open the query, I would like to be able to select all dates or a particular date.

In the criteria for the [Date_New] field, I wrote the expression,
"IIf([For Date]="",>#1/1/2014#,[For Date])"
When I open the query, I am prompted for [For Date] as expected. The query will give me the data for a date that I enter. However, I had hoped it would give me all dates if I left the [For Date] blank and simply pressed "enter".
I have also tried "IIf Isnull([For Date]............ but I get the same results.
What am I doing wrong? Or am I going about this the wrong way?

Thanks for your help, Bill

 

[SkipVought]

hi,

how about

"IIf([For Date] [highlight #FCE94F]is null[/highlight],>#1/1/2014#,[For Date])"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PHV]

The criteria should be:
=[For Date] OR Trim([For Date] & "")=""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[billheath]

Thank you PHV, I would have never gotten that on my own!
Skips might have worked also. I never tried that one. Thanks to you both!!

 

[dhookom]

For once, I don't think Skip's suggestion will work since the operator ">" is inside the expression.

I would use the following if there are no nulls in Date_New.

Nz([For Date],[Date_New])

Duane
Hook'D on Access
MS Access MVP