IIF query expression using Like "*" for the True condition

0
votes

I have a database which tracks employee QA. I'd like to be able to search by a single Staff Member, a whole team, or a Unit. I have three controls that correspond to those fields and only one can ever have a value at once. In my quesry I'd like to have threee expressions that will limit my results by one of those three fields. I'm adding just one to start and I've hit a problem.

I found this https://www.acuitytraining.co.uk/microsoft-training-courses/access/if-statements/ which seems to do what I want. Here is the code I'm trying. 

IIf(IsNull([Forms]![MainMenu]![btnManagersMenu].[Form]![cmbStaffSelect]), 
[UserLogin] Like "*",[UserLogin]=[Forms]![MainMenu]![btnManagersMenu]. 
[Form]![cmbStaffSelect])

Which works fine if the control has a value. (condition is false) If the dropdown has no value (condition is true) I get zero results. I suspect the problem lies with the Like "*" on my UserLogin field. Here is my query wizard and the buildler wizard for the IIF expression A picture of the query wizard

Can anyone see why I'm not getting any results for the dropdown control being empty. To my thinking this should give me an unfiltered list of results. I have double checked my data and there are 137 records that should appear if I'm not limited by the staff selection.

The short version of this is if cmbStaffSelect has a value I want my records limited by that value. If cmbStaffSelect is blank I want to get all records.

3
ms-access
Use a WHERE clause in your query instead. Like WHERE [UserLogin] = [Forms]![MainMenu]![btnManagersMenu].[Form]![cmbStaffSelect] OR [Forms]![MainMenu]![btnManagersMenu].[Form]![cmbStaffSelect] Is Null - Rene
I'm afraid I don't know SQL really. Would I put this whole statement in as an expression in the query builder, or would I put it in as two criteria under the field UserLogin? - Robert Loughrey

3 Answers

1
votes

Keep in mind that the iif function will always evaluate both the then and else arguments, before returning the appropriate value depending on the value returned when evaluating the supplied test expression.

As such, if either the then or else arguments have the potential to error when evaluated (regardless of the result of the evaluation of the test expression), then the iif expression has the potential to error.

As an alternative, you could use the Nz function to achieve the same result:

[UserLogin] LIKE Nz([Forms]![MainMenu]![btnManagersMenu].[Form]![cmbStaffSelect],"*")
0
votes

Perhaps your IsNull([Forms]![MainMenu]![btnManagersMenu].[Form]![cmbStaffSelect]) is always returning false because cmbStaffSelect might be equal to empty string?

Try something like this: IIf(Trim([Forms]![MainMenu]![btnManagersMenu].[Form]![cmbStaffSelect] & "") = "", [UserLogin] Like "*",[UserLogin]=[Forms]![MainMenu]![btnManagersMenu]. [Form]![cmbStaffSelect])

This checks to see if the cmbStaffSelect is "" ... if cmbStaffSelect is null - it converts it to "" by appending an "" to the null value.

0
votes

I believe your hunch is exactly correct. If you want your query result to return the * symbol for the UserLogin field; then alter your IIF statement to be: [UserLogin] = "*"