IIF in Query

I have a query that derives its criteria from a form. On the form I have 2
fields: Beginning Date and Ending Date.

Here's what I want to accomplish: If the Start date is null, then have it
use =End Date; otherwise if the Start date and End date are filled in, I
need it to say Between start date and end date.

iif(isnull(Forms!frmMyForm![txtStartDate]),tblMyTable!dtmStartDate =
Forms!frmMyForm![txtEndDate], tblMyTable!dtmStartDate between
Forms!MyForm!txtStartDate and Forms!frmMyForm!txtEndDate)

It seems that it doesn't recognize the operators of less than or equal to
nor the Between statement.

Either I am writing the syntax incorrectly or it cannot be done in an IIF
statement.

Thanks,
D.Collins

D Collins wrote:

I have a query that derives its criteria from a form. On the form I have 2
fields: Beginning Date and Ending Date.

Here's what I want to accomplish: If the Start date is null, then have it
use =End Date; otherwise if the Start date and End date are filled in, I
need it to say Between start date and end date.

iif(isnull(Forms!frmMyForm![txtStartDate]),tblMyTable!dtmStartDate =
Forms!frmMyForm![txtEndDate], tblMyTable!dtmStartDate between
Forms!MyForm!txtStartDate and Forms!frmMyForm!txtEndDate)

It seems that it doesn't recognize the operators of less than or equal to
nor the Between statement.


Son't even try to use Between in this kind of situation.
Use = and = instead.

I think I would use this kind of criteria:

=Nz(Forms!frmMyForm!txtStartDate,#1/1/1000#) AND
=Forms!frmMyForm!txtEndDate
--
Marsh
MVP [MS Access]

Marshall,

Thank you so much. I knew there had to be a way to do this. I really
appreciate it.

Thanks,
D.

"Marshall Barton" wrote:

D Collins wrote:

I have a query that derives its criteria from a form. On the form I have 2
fields: Beginning Date and Ending Date.

Here's what I want to accomplish: If the Start date is null, then have it
use =End Date; otherwise if the Start date and End date are filled in, I
need it to say Between start date and end date.

iif(isnull(Forms!frmMyForm![txtStartDate]),tblMyTable!dtmStartDate =
Forms!frmMyForm![txtEndDate], tblMyTable!dtmStartDate between
Forms!MyForm!txtStartDate and Forms!frmMyForm!txtEndDate)

It seems that it doesn't recognize the operators of less than or equal to
nor the Between statement.


Son't even try to use Between in this kind of situation.
Use = and = instead.

I think I would use this kind of criteria:

=Nz(Forms!frmMyForm!txtStartDate,#1/1/1000#) AND
=Forms!frmMyForm!txtEndDate
--
Marsh
MVP [MS Access]