Query criteria

[Guest]

Hi all

I have two unbound fields on a form. One contains a date field (startdate)
which defaults to last week's Monday's date, the other (cboItinerary) is
blank unless a date value is chosen from the combo box by a user.

I have an IIf statement in a query that stipulates if field cboItinerary is
null then it uses the startdate value to search on, if not, it uses the data
entered in cboItinerary.

The query returns no results though if I leave cboItinerary blank when I
know there should be records, it used to work on just startdate until I tried
adding the cboItinerary field to the equation. Syntax below if someone can
help please?

IIf(IsNull([forms]![frm Expenses
Menu]![cboItinerary]),[Itinerary].[ReviewDate] Between [forms]![frm Expenses
Menu]![StartDate] And [forms]![frm Expenses Menu]![StartDate]+4,[forms]![frm
Expenses Menu]![cboItinerary])

Thanks in advance for any help.
Sue

 

[Guest]

Try this

Where [Itinerary].[ReviewDate] Between IIf(IsNull([forms]![frm Expenses
Menu]![cboItinerary]), [forms]![frm Expenses Menu]![StartDate], [forms]![frm
Expenses Menu]![cboItinerary]) And IIf(IsNull([forms]![frm Expenses
Menu]![cboItinerary])[forms]![frm Expenses Menu]![StartDate]+4,[forms]![frm
Expenses Menu]![cboItinerary])

 

[Guest]

Thanks that worked

Sue

Try this

Where [Itinerary].[ReviewDate] Between IIf(IsNull([forms]![frm Expenses
Menu]![cboItinerary]), [forms]![frm Expenses Menu]![StartDate], [forms]![frm
Expenses Menu]![cboItinerary]) And IIf(IsNull([forms]![frm Expenses
Menu]![cboItinerary])[forms]![frm Expenses Menu]![StartDate]+4,[forms]![frm
Expenses Menu]![cboItinerary])

--
\\// Live Long and Prosper \\//
BS"D

Hi all

I have two unbound fields on a form. One contains a date field (startdate)
which defaults to last week's Monday's date, the other (cboItinerary) is
blank unless a date value is chosen from the combo box by a user.

I have an IIf statement in a query that stipulates if field cboItinerary is
null then it uses the startdate value to search on, if not, it uses the data
entered in cboItinerary.

The query returns no results though if I leave cboItinerary blank when I
know there should be records, it used to work on just startdate until I tried
adding the cboItinerary field to the equation. Syntax below if someone can
help please?

IIf(IsNull([forms]![frm Expenses
Menu]![cboItinerary]),[Itinerary].[ReviewDate] Between [forms]![frm Expenses
Menu]![StartDate] And [forms]![frm Expenses Menu]![StartDate]+4,[forms]![frm
Expenses Menu]![cboItinerary])

Thanks in advance for any help.
Sue