Can I use a conditional n IIF statement in the Criteria box of a Query By Example (QBE)?

Posted on 2006-07-12
Last Modified: 2012-05-05
Let say I have a table (tblData) or query (qryData) and people can only see records of their own Using organization.  For example, if I belong to Using organization "U0011", I can only view records such as:
UsOrgID  Model    S/N      etc.
U0011      ABC     0001     ...
U0011      DEF     0100     ...
U0011      ABC     0002     ...

But two Users, U0000 and U0036, should be able to view the records of all Users.  How can I put this condition in the SQL?

For example, is there any way I can get this query to work: SELECT tblData.*, *  FROM tblData WHERE tblData.UsOrgID=IIf([txtUser]="U0000" or [txtUser]="U0036","Like 'U*'",[txtUser])

In other words, IIf([txtUser]="U0000" or [txtUser]="U0036","Like 'U*'",[txtUser]), is in the QBE criteria box of the UsOrgID field.

Any ideas?
Question by:bobbat
  • 4
  • 3
  • 2
  • +3
LVL 44

Accepted Solution

Leigh Purvis earned 250 total points
ID: 17093283
Is txtUser a control on a form - or a field?

If it's a field - or a control and the SQL is local to the form also then

Like IIf([txtUser]="U0000" or [txtUser]="U0036","U*",[txtUser])
LVL 11

Expert Comment

ID: 17093313
If you had a log in wher users can be identified, you could store organization info such as "U0011" or, in the case of "U0000" or "U0036" you could store "*". Then the query would contain, in its criteria:

Like Forms!FormName!UserName

In the case of "U0011" they would only see records pertaining to them. Users "U0000" and "U0036" would see everything since the "*" would be passed to the criteria.

Make sense?
LVL 65

Expert Comment

ID: 17093409
What if u created some kind of lookup table, whereby u can list users who can view all, then u have to reference that in your query. If u add another user, then fine, just add to table, no change required in query

try using IN

IIF(txtUser IN ('U0000','U0036'),"U*",txtUser)

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 65

Expert Comment

ID: 17093419
urm, scrap the idea of a lookup table, I figure EMCIT has already specified that, sorry EMCIT
LVL 44

Assisted Solution

GRayL earned 250 total points
ID: 17093576
How about:

SELECT tblData.*, *  FROM tblData tblData.UsorgID Like Switch ([EnterOrgID] IN ('U0000','U0036'),"*", [EnterOrgID] NOT IN ('U0000','U0036'), [EnterOrgID]);

Author Comment

ID: 17096725
To LPurvis: txtUser is actually a control or object on an Active Server Page (ASP).  When I put my cursor on it, the following code is highlighted using FrontPage 2003: <%=Request.Cookies("ADMIN")("N")%>

Your VBA SQL works: SELECT qrySearchItemList.*, * FROM qrySearchItemList WHERE (((qrySearchItemList.User) Like IIf([txtUser]="U0000" Or [txtUser]="U0036","U*",[txtUser])));

To GRayL:  Your VBA SQL works when I added the word "WHERE" and changed EnterOrgID to txtUser:
SELECT qrySearchItemList.*, * FROM qrySearchItemList WHERE (((qrySearchItemList.User) Like Switch([txtUser] In ('U0000','U0036'),'*',[txtUser] Not In ('U0000','U0036'),[txtUser])));

To All:  Thank you for your time and advice.   My ultimate goal was to change a query in an ASP file.  With my lack of ASP or VBscript knowledge, I thought all I had to do was add some words to the existing ASP query that works which is: fp_sQry="SELECT * FROM qrySearchItemList WHERE (User =  '::txtUser::')"  

So I tried fp_sQry="SELECT * FROM qrySearchItemList WHERE (((qrySearchItemList.User) Like IIf('::txtUser::' = 'U0000' Or '::txtUser::' = 'U0036','U*','::txtUser::')))" and others

but none worked.  So I tried the ASP forum and have not been able to translate any or your VBA queries to a VBscript query.  If you can help me, please go to the ASP forum at:
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17097909
Your VBScript probably uses an ADO connection to the data though - yes?
In which case you'd use % as the wildcard character - not *.  i.e. searching on 'U%'.
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17121366
And in VBScript you'll just need to grab the string using your textbox value however you've chosed to do so (never used any default Frontpage functionality before).
Say, if you pass the textbox value in the querystring then
strVal = Request.QueryString("txtUser")
strSQL = "SELECT qrySearchItemList.* FROM qrySearchItemList WHERE qrySearchItemList.User Like IIf([txtUser]='U0000' Or [txtUser]='U0036', 'U%', " & strVal  & ");"

Author Comment

ID: 17121561
I will try it as soon as I get my localhost working again.  I have a "conflict on Port 80."  Four hours with MS second level support did not help--they referred me to HughesNet, my satellite ISP.  Hughes second level support/engineering says I do not have a conflict because their DW7000 modem communicates with Port 80.  I can use the regular Internet fine but local host responds with "This Page cannot be displayed."

So I have done:  System Clean, Defragment, Chkdsk, HijackThis, TCPView, fPort, Full System Scans with: Norton Antivirus, MS Defender, Ad-Aware SE, MS malicious software removal tool, recycled the power & rebooted etc.

Last Wednesday, while using FrontPage, Norton Antivirus kept opening a dialog box in the lower right corner of my screen.  It appeared every five seconds for several minutes so I finally read it and it said I was under attack but “not to worry” because I was protected with autoblock.  There was even a check box not to show the warning again.  So I closed FrontPage and reopened it and tried my localhost and local website again.  Now I get the “The page cannot be displayed” / “Cannot find server” error messages.  But I was just there a few minutes ago!

According to Norton, I was attacked by ‘HTTP MS FrontPage Remote Debug Buffer Overrun’ which “. . . will allow the attacker to influence the values in the ECX and EDI registers, which may be leveraged to execute arbitrary code.”

HP recommends I use a procedure from Microsoft for repair.  The vulnerability to this attack was fixed years ago via Windows XP (SP1) and all my OS, application, antivirus, Ad-Aware SE, MS defender, MS malicious software removal tool, etc. software is up to date.  I ran all antivirus and scan software again and no infected items were found.  

Could it be coincidence that my localhost stopped working after the attack—could something else like corruption be the cause?

Any ideas? Seems like a rebuild is the only procedure left to try . . .
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17462116
Hi sorry - a rebuild of what?

Author Comment

ID: 17474925
Rebuild the computer by using the restore CDs that came with the computer when purchased.  In the process of restoring the computer to the factor configuration and then reloading all the software and updates, I lost track of this question and should have closed it myself after using the query below:

Answer (The following query works):

PARAMETERS prmUser Text ( 5 );
FROM qrySearchItemList
WHERE IIf([prmUser]='U0000',1=1,IIf([prmUser]='U0036',1=1,User=prmUser));

I apologize to all for not closing the question and agree with whatever mbizup proposes.
LVL 61

Expert Comment

ID: 17481114
Hmm... I'd actually intended to select Leigh's first response here.  

I'd based the recommendation on bobbat's statement that both Lpurvis's and GrayL's posts led to working results within the Access environment, answering the question within it's original context (I saw the asp angle as the start of a new question).  If that sounds fair to everyone, I'll correct the recommendation as follows:

  Split: LPurvis {http:#17093283} & GRayL {http:#17093576}  

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question