Do nothing criteria

[boboivan]

Hi guys!

I use the following criteria in a query for a specific field:

Like IIf([TempVars]![CurentUser]="ADMIN","*",[TempVars]![CurentUser])

The join properties of the tables in the query is set up on 2.
If the current user is admin, it returns all values, except of the null values.

How can I make it return everything (including the null values)?

Many thx!

[ranman256]

button click event:

if admin then
docmd.openquery "qsAllRecs"
else
docmd.openquery "qs1User"
endif

[boboivan]

Thx ranman256!

The query opens a combo box list. What should I have in the row source?

[CJ_London]

or you could try

myfield like [TempVars]![CurentUser] or [TempVars]![CurentUser]="ADMIN"

[boboivan]

I'm sorry Ajax, but I didn't get it. What do you mean?

[CJ_London]

You didn't provide the full criteria

myfield like [TempVars]![CurentUser] or [TempVars]![CurentUser]="ADMIN"

the bit in red replaces this

Like IIf([TempVars]![CurentUser]="ADMIN","*",[TempVars]![CurentUser])

so if current user is ADMIN, all records are returned. If currentuser <> ADMIN then only those records relating to the current user are returned

[boboivan]

Thx Ajax!

This is the full criteria, that is filtering a field in the query which is the row source for a combo box.
By simply replacing the Like IIf([TempVars]![CurentUser]="ADMIN","*",[TempVars]![CurentUser]) with Like [TempVars]![CurentUser] or [TempVars]![CurentUser]="ADMIN", the query returns nothing.

[CJ_London]

This is the full criteria

well actually it's not. A criteria is what appears after the word WHERE - all you are showing is some of it.

So without seeing the full sql, it is impossible to comment

Also, to be clear -

1. what is the value of [TempVars]![CurentUser] when you run your query?
2. what happens if you remove the WHERE clause - are all records returned?
3. have you confirmed [TempVars]![CurentUser] is not null? - easy way to do this is to remove your where clause and include [TempVars]![CurentUser] as a field i.e. something like

SELECT *, [TempVars]![CurentUser] as TC
FROM myTable

4. can you confirm your combobox is set up to show the required values - and does so if the where clause is removed

[boboivan]

Hi Ajax!

Sorry for the delay.

I don't know what happened when I first tried your criteria and didn't work. Now is working great.

Thx a lot!

[CJ_London]

got there in the end!

[boboivan]

Yeah!

Thx again Ajax!