combo box not returning values when using wildcard

[bill s]

I have pounded my head against something that should be simple. In my combo box where I am returning a row source using the query builder, I have the code below. In this case, IF the txtEmpRole is "OOEUSER", it returns records as expected. However if the txtEmpRole is not OOEUSER, then I get nothing despite the wildcard. Please tell me what I am doing wrong. Thank you!

IIf(([Forms]![OoeEntryForm]![txtEmpRole])="OOEUSER",([Forms]![OoeEntryForm]![txtEmpId]),([CROSSWALK].[EMPID]) Like "*")

[June7]

Dynamic operators (=, <>, LIKE) will not work. The operator must be fixed. And including the field in the expression is definitely bad syntax.

Why not just use the LIKE and wildcard?

LIKE Forms!OoeEntryForm!txtEmpRole & "*"

[Missinglinq]

Also, is EMPID defined as a Text Field or as a Number Field? IIRC, the Like operator can only be used against a Text Field...not against a Numerical Field.

Linq ;0)>

[bill s]

Thank you for replying.

I have a login. The login form passes the txtEmpRole and the txtEmpId.
If the user roll is "ooeuser", I then want empid = their txtEmpID (which only show the users their own transactions)
However, if the userroll is not "ooeuser", then it would be a manager (ooemgr) and I want to show all transactions.

the first part of the if statement works fine (IIf(([Forms]![OoeEntryForm]![txtEmpRole])="OOEUSER",([Forms]![OoeEntryForm]![txtEmpId]),

But I cannot get the "else" part of this to work. If I put a value such as 2 (2 being an empID), it will work (2 is not what I want to show but I tested to make sure something would return) . If I put >0, it doesn't work, nor does *.

This below will not return any records. (note that empid is a numeric field)
IIf(([Forms]![OoeEntryForm]![txtEmpRole])="OOEUSER",([Forms]![OoeEntryForm]![txtEmpId]),"*")


Overall , I am simply trying to filter what the user sees based on their role. If they are a user, then they only see their own. If they are a manager, then they see all users.

[bill s]

and yes, it's numeric

This returns value if txtEmpRole is OOEUSER
IIf(([Forms]![OoeEntryForm]![txtEmpRole])="OOEUSER",([Forms]![OoeEntryForm]![txtEmpId]),([CROSSWALK].[EMPID]) Like "*")

This returns value if txtEmpRole is not OOEUSER but it returns only userID = 2
IIf(([Forms]![OoeEntryForm]![txtEmpRole])="OOEUSER",([Forms]![OoeEntryForm]![txtEmpId]),2)

How can I return ALL UserIDs verus just user 2 in this example? I would think replacing the 2 with a * would work, but I get nothing returned.

I am ready to change careers... :/

[InsuranceGuy]

I have pounded my head against something that should be simple. In my combo box where I am returning a row source using the query builder, I have the code below. In this case, IF the txtEmpRole is "OOEUSER", it returns records as expected. However if the txtEmpRole is not OOEUSER, then I get nothing despite the wildcard. Please tell me what I am doing wrong. Thank you!

IIf(([Forms]![OoeEntryForm]![txtEmpRole])="OOEUSER",([Forms]![OoeEntryForm]![txtEmpId]),([CROSSWALK].[EMPID]) Like "*")

Try adjusting your criteria to

LIKE IIf(([Forms]![OoeEntryForm]![txtEmpRole])="OOEUSER",([Forms]![OoeEntryForm]![txtEmpId]),"*")

[June7]

I NEVER use dynamic parameterized queries. I prefer http://www.allenbrowne.com/ser-62.html

[bill s]

that gives me an error "expression is incorrectly or too complicated to be evaluated."

[InsuranceGuy]

Bill, that's an indication of string vs numeric comparison. I see in other posts that you have numeric field, so you can't use a like-string comparison. You will need to adjust course choose one method, such as June's reference.

[orange]

Bill,
Perhaps, if readers had some business context - a description in plain English about what you are trying to do - they could provide more focused responses and even suggest a few alternatives.

I understand this situation

I have pounded my head against something that should be simple.

but would like to hear more of the issue/problem in business terms.