IIF Statement - How to specify NO CRITERIA

  • Thread starter Thread starter reddy
  • Start date Start date
R

reddy

I have a query that obtains its critieria from a form.

Select *
From tblData
Where Date=[Forms]![FrmMonthlies]![CmbDate]

If no date is chosen on the Form, I want the Date criteria in the Query to
be blank.

I tried this:
Select *
From tblData
Where Date=
IIF([Forms]![FrmMonthlies]![CmbDate]="","",[Forms]![FrmMonthlies]![CmbDate])

This gives me an error. Please advise.
 
Where [Date] Like IIf(IsNull([Forms]![FrmMonthlies]![CmbDate], "*",
[Forms]![FrmMonthlies]![CmbDate])

Note the brackets around the field name Date
Date is a reserved word and should not be used as a name. It will cause
Access some confusion. Enclosing it in brackets will usually, but not
always, avoid the problems.
The best thing to do would be to change the name to something meaningful.
It not only avoid the use of a reserved word, but it gives you and anyone
else that may have to work on the application an idea of what it is used
for:
SaleDate EndDate BirthDate or whatever.
 
I have a query that obtains its critieria from a form.

Select *
From tblData
Where Date=[Forms]![FrmMonthlies]![CmbDate]

If no date is chosen on the Form, I want the Date criteria in the Query to
be blank.
Click to expand...

Try

Select *
From tblData
Where [Date]=[Forms]![FrmMonthlies]![CmbDate]
OR [Forms]![FrmMonthlies]![CmbDate] IS NULL

Note that Date is a VERY BAD choice of fieldname, as it's a reserved word for
the builtin Date() function - Access *will* get confused.
 
Back
Top