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)

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 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