Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

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

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
bobbat
Asked:
bobbat
  • 4
  • 3
  • 2
  • +3
2 Solutions
 
Leigh PurvisDatabase DeveloperCommented:
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
 
EMCITCommented:
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
 
rockiroadsCommented:
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
rockiroadsCommented:
urm, scrap the idea of a lookup table, I figure EMCIT has already specified that, sorry EMCIT
0
 
GRayLCommented:
How about:

SELECT tblData.*, *  FROM tblData tblData.UsorgID Like Switch ([EnterOrgID] IN ('U0000','U0036'),"*", [EnterOrgID] NOT IN ('U0000','U0036'), [EnterOrgID]);
0
 
bobbatAuthor Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
bobbatAuthor Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
Hi sorry - a rebuild of what?
0
 
bobbatAuthor Commented:
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
 
mbizupCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now