IIF inserting <>False

[Pisteuo]

The following IIF statement is not producing expected results. Additionally, Access is creating a new field named after the IIF string and inserting "<>FALSE" under that field's criteria. "<> FALSE" is only visible in the wizard and not in the SQL.

Summary:
((Other Criteria) AND ((IIF(txttest="Test", Field BETWEEN txtDate AND txtDateEnd, Field BETWEEN DateValue("1/1/2009") AND txtDateEnd)))

SQL:
...And ((IIf(forms!frmLookAhead!txtTest="Test",tblDates.fldWalkdown Between forms!frmLookAhead!txtDate And forms!frmLookAhead!txtDateEnd,tblDates.fldWalkdown Between DateValue("1/1/2009") And forms!frmLookAhead!txtDateEnd))));

Thank you.

 

[Galaxiom]

A query cannot use a function or variable to define field names, table names or where conditions.

You can build the query in VBA and then run it but that is really working around a clumsy strategy. I can't tell exactly what you are trying to do from the question but you probably need to change the values in the date text boxes to reflect the logic you require.

 

[Pisteuo]

I did have this function in a WHERE condition.

How is the IIF function utilized to define criteria if not in a WHERE condition?

 

[Galaxiom]

The IIF function is used to select between two values based on the condition. It cannot modify the structure of the query itself.

SELECT IIF({condition},"A","B") AS somename, [field3]
FROM sometable;

SELECT IIF({condition},[field1],[field2]) AS somename, [field3]
FROM sometable;

but not

SELECT IIF({condition},[field1], [field2]), [field3]
FROM sometable;

SELECT [field1], [field2], [field3]
FROM IIF({condition},[table1],[table2]);

SELECT [field1],[field2], [field3]
FROM sometable
WHERE IIF({condition}, [field1]="A", [field2]="B");