Using the Criteria field in a query

[Linda354]

I have a query set to show only those files created by CurrentUser(). I want to also set it to show all files for the Administrator, Bruce. How do I code that?

It seems like I need to tell the query that if CurrentUser = Bruce, then there is no criteria. Is this something I can't put into the query, but need a module for? Where would that be called from?


I hope I can just use the query form, since I don't know VBA yet.

 

[SgtJarrow]

I am going to assume you are using a function CurrentUser() as part of your criteria as this it what it looks like in your post. I am also going to assume that that function returns a user name such a George, Mary, or Mark. Since Bruce is merely another user name....you can set your criteria as follows...

CurrentUser() Or "Bruce"

This should list everything by current user and Bruce. Hope this helps. Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[Linda354]

That's what I tried at first, then I realized what it was saying is that Bruce will only see Bruce's files, since when he's signed on CurrentUser() = Bruce. It's just a redundant statement.

I need to be saying

If CurrentUser()<> &quot;Bruce&quot;
Criteria is
CurrentUser()

Would this work in the Criteria field?

CurrentUser() WHERE CurrentUser() <> &quot;Bruce&quot;

 

[JoJ]

Try this syntax in your criteria field:

=Iif(CurrentUser()<>&quot;Bruce&quot;,CurrentUser())

 

[Linda354]

Thanks so much, I was really happy to get some real syntax into this project. I could even read the logic, but it didn't work! Seems like it should. But when I signed on as Bruce it showed me nothing, since Bruce hasn't input anything yet. When I signed on as another user, the records he input came up. In other words, it's behaving as if the only thing there was Criteria: CurrentUser()

 

[Kanga35]

I think you're on the right track with one condition missing, the syntax is the condition first then if it's OK then if it's not OK... maybe something like:

=Iif((CurrentUser()<>&quot;Bruce&quot,CurrentUser(),CurrentUser()& &quot;Bruce&quot

 

[Kanga35]

Heuuuuu, I think that it should be OR instead of & in the above

 

[Linda354]

Thanks for the suggestion. It came back a Data Mismatch when I tried to run the form.

The &quot;Zoom&quot; window, after saving, turned it into:

IIf((CurrentUser()<>&quot;Bruce&quot,CurrentUser(),([Client Contacting Information].[CounselorID])=CurrentUser() Or ([Client Contacting Information].[CounselorID])=&quot;Bruce

Does this have anything to do with not declaring what kind of variable CurrentUser is?

 

[Linda354]

Can anybody out there figure out what might be wrong?

&quot;The syntax is the condition first then if it's OK then if it's not OK... maybe something like:

=Iif((CurrentUser()<>&quot;Bruce&quot,CurrentUser(),CurrentUser() OR &quot;Bruce&quot

Is the above logic what we need? If the current user is not Bruce, then (show only files for the) Current user. If it is Bruce then all files should be shown. Does the last part of that sentence say that?

 

[SgtJarrow]

No...the statement reads

If current user is not Bruce show files for current user only. Otherwise show files for current user and Bruce.

Is this what you are trying to say??? Or do you want Bruce to see everything (All users plus himself)????? Programming isn't a profession of choice.
It's a profession of calling...
&quot;Hey Programmer, your application broke again!&quot;

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[Linda354]

&quot;Or do you want Bruce to see everything (All users plus himself)?????&quot;

Yes, I want Bruce to see everything. What do I say in that third part of the Iif statement that will say , &quot;forget it, there's no criteria here.&quot;

 

[SgtJarrow]

Here you go....this will produce the results....

Like IIf([Forms]![Form2]![Text0]<>&quot;Bruce&quot;,[Forms]![Form2]![Text0],&quot;*&quot

The Like, combined with the IIf and the &quot;*&quot; for a false IIf made the difference.... Programming isn't a profession of choice.
It's a profession of calling...
&quot;Hey Programmer, your application broke again!&quot;

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[SgtJarrow]

Sorry...to match your sytax it should read:

Like IIf(CurrentUser()<>&quot;Bruce&quot;,CurrentUser(),&quot;*&quot



Programming isn't a profession of choice.
It's a profession of calling...
&quot;Hey Programmer, your application broke again!&quot;

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com