Syntax error with Wildcard ? in a query expression

[jyellis]

IIf([User Id]=”1???????”,[User ID], IIf([User Id]=”C??????”,[User ID],IIf([User ID]=”No User ID”,””)))

I get a syntax error on the first question mark.

If the length=8 and begins with a 1, then [User ID]
If the length =7 and begins with a C, then [User ID],
if the field contains "No User ID", then return nothing.



I have more criteria but that part works; it's just this first part.

[JoeM]

Try a structure like:

IIF((Left([User iD],1)="1") And (Len([User Id])=8),[User Id],...

For the next check (length 7, starting with "C"), you could nest it like you were, or combine it with the first one using OR.

[Micron]

Also check your quotation marks. If that's what you really have, I have seen this cause problems before (backwards or forwards leaning quotes).

if the field contains "No User ID", then return nothing.

OK, this isn't the cause of the error, but might raise an issue soon. "" is not "nothing". Nor is it Null. So your query might not function properly if the empty string you're passing to it comes in to play.