Access experience=advanced
Hi,
I'm trying to use IIF statement in the criteria of a field in a query. The field is called "Data From" and contains data such as "Job Sheets" & "Reception Database"

My IIF statement in the criteria section of the "Data From" field is

IIf([Forms]![FrmJobSheetsEntry]![ShowOnlyJobSheetsData]=True,"Job Sheets",Is Not Null)

The form FrmJobSheetsEntry contains a check box. If box is ticked, the query returns rows where "Data From" contains "Job Sheets", but unticked it does not return anything. I want it to return all rows. "Data From" field does not contain any null values.

How can I do this ?

Thanks for your help
 

---

Answer from Richard Rost:

You're using the IIF statement incorrectly. Plus, "Is Not Null" can't be used to set a value like that.

Here's what you should do: make an AfterUpdate event for your checkbox. In it say:

If ShowOnlyJobSheetsData = TRUE then
Me!RecordSource = "SELECT * FROM TableName WHERE [Data From]='Job Sheets'"
else
Me!RecordSource = "SELECT * FROM TableName"
end if

Obviously replace TableName with the name of your table.

Also, these tutorials will probably help you:

http://599cd.com/tips/access/iif-function?key=BlFAlEx

http://599cd.com/tips/access/afterupdate-event-vba?key=BlFAlEx