Query Criteria -- iif Function (1 Viewer)

Vonik · Aug 17, 2014

I want the Query Criteria to pull its value from a control on a form.
The form control either has data or is null. (My problems occur when the form control is Null)
The field in the table either has data, is null or is blank.

This is what im trying to get to work.

Code:

=IIf(IsNull([Forms]![FormName]![FormControl]),"" Or Is Null,[Forms]![FormName]![FormControl])

This works for the records with fields that are blank.

Code:

=IIf(IsNull([Forms]![FormName]![FormControl]),"",[Forms]![FormName]![FormControl])

This works for the records with fields that have data.

Code:

Like IIf(IsNull([Forms]![FormName]![FormControl]),"*",[Forms]![FormName]![FormControl])

This works for the records with fields that are Null or Blank if i drop the iif function but then i lose the ability to pull criteria data from the form control.

Code:

"" Or Is Null

All help is greatly appreciated.

Thank You,

pbaldy · Aug 17, 2014

How about

http://access.mvps.org/access/queries/qry0001.htm

Vonik · Aug 17, 2014

Thanks, I can't believe it was that easy.

Code:

[Forms]![myform]![myControl] OR [Forms]![myForm]![myControl] Is Null

Vonik · Aug 17, 2014

Can anyone tell me why this code didn't work when the iif function is true?

Code:

IIf(IsNull([Forms]![FormName]![FormControl]),"" Or Is Null,[Forms]![FormName]![FormControl])

Shouldn't the query criteria be read as

Code:

"" Or Is Null

in that case?

Query Criteria -- iif Function (1 Viewer)

Vonik

Registered User.

pbaldy

Wino Moderator

Vonik

Registered User.

Vonik

Registered User.

Similar threads

Users who are viewing this thread