If Statement in query

[Jules Decouls]

Hi

I am trying to use an iif statement to limit the results of a query based on the value of a record. This is the criteria in the query field CTRID (centre ID)

=IIf([Forms]![POMSfrmMainMenu]![Centre]=0,>0,[Forms]![POMSfrmMainMenu]![Centre])

when users login their login details are stored in a field called CTRID. Admin users have a value of 0, other users have a value of 1 or 2 or 3 etc. which relates to their particular store. the query shows orders for all stores, but I want the user to only see their own store orders unless they are an admin user, then I want them to see all orders.

If I put >0 in the criteria for the query, I see all stores. If I put the criteria as
[Forms]![POMSfrmMainMenu]![Centre]) then i limit the view to only these stores, but if i use the expression above - I return no records.

how can I put the >0 into the iff statement so that it works. I've tried putting ">0" but thsi doesnt work.

Any help would be much appreciated!

Thanks

 

[CJ_London]

you cannot build criteria in that way

you would need something like

[Forms]![POMSfrmMainMenu]![Centre] OR [Forms]![POMSfrmMainMenu]![Centre]=0

 

[plog]

I would create a calculated field to determine their access to the record and put the criteria there, not in the criteria area:

UserAccess: IIf([Forms]![POMSfrmMainMenu]![Centre]=0 OR [Forms]![POMSfrmMainMenu]![Centre]=[CTRID], 1,0)

Then in the criteria section of that field I would put 1.