iif statement with query criteria (1 Viewer)

[Thicko]

Hi All,

I have a Form frmAuditTrail, it has three combo boxes in the header (Username, Area & Action)

What I'm attempting to do is if an Area is selected then I want the Combo box for Action to only list Actions associated with the Area. The difficulty comes when if no Area is select then I want all actions to be listed in the Action Combo Box.

I have been unsuccessful with:

IIf(IsNull([Forms]![frmAuditTrail]![Area]),Like "*",[Forms]![frmAuditTrail]![Area])

Please can someone brighter than me suggest an alternative

Many Thanks
Thicko

 

[arnelgp]

you can just simple filter it like this:

Like NZ([Forms]![frmAuditTrail]![Area], "*")

 

[theDBguy]

Hi Thicko. Here's another option, in case you want to try it. Hope it helps...

[Forms]![frmAuditTrail]![Area] OR [Forms]![frmAuditTrail]![Area] Is Null

 

[Thicko]

Thank you gents, both solutions worked as intended.

 

[theDBguy]

Thank you gents, both solutions worked as intended.

Hi. You're welcome. Arnel and I were happy to assist. Good luck with your project.

 

[Pat Hartman]

My preference is theDBguy's solution since using LIKE frequently results in the query engine not being able to use any index. As long as your table is small, you won't notice but larger tables will definitely be faster when your searches can use any available indexes.