Help with a IIF statement in a Query

[jyellis]

Here is the scenario:

In my UserID field:
1. If it is not null then return the current value

Then handle the null fields like this:
2. If Username contains Loaner then UserID = Loaner
3. If Username contains Mifi then UserID = Mifi
4. If Username contains Alarm then UserId = Alarm

5. All other blanks leave empty

I start with 1777 the query works but returns only 1699. My formula is not returning the blank UserID records from step 5.



Here is my formula:

IIf([UserID] Is Not Null,[UserID],IIf([User Name] Like "*Loaner*","Loaner",IIf([User Name] Like "*Mifi*","MIFI",IIf([User Name] Like "*Alarm*","Alarm",IIf([User Name] Like "*Modem*","Modem","")))))


Using Access 2016
Thank you in advance

[John_G]

You don't have the right syntax in the first check for Null. Try:

IIf(not isnull([UserID]) ,[UserID],IIf([User Name] Like "*Loaner*","Loaner",IIf([User Name] Like "*Mifi*","MIFI",IIf([User Name] Like "*Alarm*","Alarm",IIf([User Name] Like "*Modem*","Modem","")))))

[jyellis]

Thank you for the correct syntax:
IIf(Not IsNull([UserID]),[UserID],IIf([User Name] Like "*Loaner*","Loaner",IIf([User Name] Like "*Mifi*","MIFI",IIf([User Name] Like "*Alarm*","Alarm",IIf([User Name] Like "*Modem*","Modem","")))))

But it is still returning only the records where UserID is not blank. (1699 out 1777) I need all of them returned. I expected the problem with was the last part of my formula - "", but not sure.

[CJ_London]

you have only provided part of the criteria - as written it could return anything but to what purpose? equal another field? true? false?

[John_G]

Where are you using this IIf, i.e. in what part of the query? To get the information you want, it would have to be an expression in a query field.

[jyellis]

Where are you using this IIf, i.e. in what part of the query? To get the information you want, it would have to be an expression in a query field.

Yes...In the [UserId] field criteria row

[jyellis]

If userid is blank and it meets criteria (loaner, mini, alarm) it returns - loaner, midi, alarm in the userid field

the query is removing the 78 records that do not meet the criteria so instead of returning all 1777 records it only includes 1699 records
in other words there should be 78 records where userid is null, but I want them included in the query

[June7]

If you want all records, then why do you have this on Criteria row? You are excluding the 78 records because they don't meet the criteria. So don't put the expression on the Criteria row.

Maybe you just want to construct a field with the calculation. Put the expression on the Field row. Here is example of alternative using Switch().

Category: Switch([User Name] Like "*Loaner*","Loaner", [User Name] Like "*Mifi*","MIFI", [User Name] Like "*Alarm*","Alarm", [User Name] Like "*Modem*","Modem", True,"None")

Why is this data included in the [User Name] field? Username and this classifying value should be in separate fields to begin with.

Advise not to use spaces in object and field names.