iif statement with query criteria

T

Thicko

Registered User.

Local time

Today, 16:00

Joined

Oct 21, 2011

Messages

61

• Aug 6, 2019

• #1

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:

Code:

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

Please can someone brighter than me suggest an alternative

Many Thanks
Thicko

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 23:00

Joined

May 7, 2009

Messages

20,335

• Aug 6, 2019

• #2

you can just simple filter it like this:

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

 

theDBguy

I’m here to help

Staff member

Local time

Today, 08:00

Joined

Oct 29, 2018

Messages

22,616

• Aug 6, 2019

• #3

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

Code:

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

 

T

Thicko

Registered User.

Local time

Today, 16:00

Joined

Oct 21, 2011

Messages

61

• Aug 6, 2019

• #4

Thank you gents, both solutions worked as intended.

 

theDBguy

I’m here to help

Staff member

Local time

Today, 08:00

Joined

Oct 29, 2018

Messages

22,616

• Aug 6, 2019

• #5

Thicko said:

Thank you gents, both solutions worked as intended.

Click to expand...

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

 

P

Pat Hartman

Super Moderator

Staff member

Local time

Today, 11:00

Joined

Feb 19, 2002

Messages

47,400

• Aug 6, 2019

• #6

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.