Query Error Msg, searching for null values

[jfmj77]

Hi,
I'm relatively new to Access and have this problem. I have a table (Agent) with a Text field called Active which contains either a Null value or an "X". I use a text box (txtCit) on a form (frmAgentReport) to supply the search criteria for a report which has the the Query as its RecordSource. In the Criteria of the Query I have the following code: iif([Forms]![frmAgentReport]![txtCrit] Is Not Null,[Forms]![frmAgentReport]![txtCrit],[Agent].[Active] Is Null). If X is entered into the text box the report runs correctly, However if a Null value is entered the following error appears; "This expression is typed incorrectly, or it is too complex to be evaluated....."
Any help would be appreciated. Thanks.

 

[jrbarnett]

Try this variation:

iif(Not IsNull([Forms]![frmAgentReport]![txtCrit]),[Forms]![frmAgentReport]![txtCrit],[Agent].[Active]).

This means: If Forms!frmAgentReport!txtCris is not null then return this, otherwise return the value of "agent"."active".

John

 

[jfmj77]

Hi John,
Thanks for your effort. The error msg no longer appears and it works with the "X" selected. However, if the Null is selected, no records are returned even though that field contains Null values.

John

 

[JonoB]

I suggest you change you field to a yes/no field (if you can). Then make the field on the form a tickbox or toggle button and link your query to this.
A tick or toggle box pressed down = -1
No tick or static toggle box = 0

Much "cleaner" way of doing things, rather than having to rely on null's.

Hope this helps.