Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2006-07-12
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
LVL 44

Accepted Solution

Leigh Purvis earned 1000 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)

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

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 1000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

670 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