Solved

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

Posted on 2006-07-12
14
518 Views
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?
0
Comment
Question by:bobbat
  • 4
  • 3
  • 2
  • +3
14 Comments
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 250 total points
Comment Utility
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])
0
 
LVL 11

Expert Comment

by:EMCIT
Comment Utility
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?
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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)

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
urm, scrap the idea of a lookup table, I figure EMCIT has already specified that, sorry EMCIT
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 250 total points
Comment Utility
How about:

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

Author Comment

by:bobbat
Comment Utility
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: http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21917506.html#17096261
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
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%'.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
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  & ");"
0
 

Author Comment

by:bobbat
Comment Utility
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 . . .
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
Hi sorry - a rebuild of what?
0
 

Author Comment

by:bobbat
Comment Utility
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 );
SELECT *
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.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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}  
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now