Help with IIF in Criteria

[Jeff Hunt]

I am trying to write a criteria for a query that changes based on whether a
checkbox on the form is checked. If the checkbox is empty it should return
only records where the criteria matches a text box on the form. If the
checkbox is marked, it should return records less than the value in the text
box. Basically, I am trying to put this iif statement in my criteria:

iif(forms!frmMain.chkOlder = 0, forms!frmMain.txtMaxDate, <
forms!frmMain.txtMaxDate)

The true condition works on this, but obviously the false condition doesn’t.
Is there a way to put a “less than†in an iif statement? Or is there a
better way to do this?

 

[tina]

try the following, as

SELECT FieldName1, FieldName2
FROM TableName
WHERE (FieldName2=Forms!frmMain!txtMaxDate AND Forms!frmMain!chkOlder=False)
OR (FieldName2<Forms!frmMain!txtMaxDate AND Forms!frmMain!chkOlder=True);

substitute the correct field and table names, of course.

hth