By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Problems writing a Query

P: 16
I am not sure how to write this correctly in the criteria of a query, could someone help me.

If ([Forms]![frmCabinetScorecard]![txtCabinet] = "Test1" Then Like ("Client Computing" or "Network Solutions"))

Thanks,

Ryan
Aug 9 '07 #1
Share this Question
Share on Google+
15 Replies


Expert 100+
P: 296
I am not sure how to write this correctly in the criteria of a query, could someone help me.

If ([Forms]![frmCabinetScorecard]![txtCabinet] = "Test1" Then Like ("Client Computing" or "Network Solutions"))

Thanks,

Ryan
Try:
iif([Forms]![frmCabinetScorecard]![txtCabinet] = "Test1", Like ("Client Computing" or "Network Solutions"),"NA")
To be perfectly honest, I've never tried referencing a form in a query, so I don't know how that will result. Also, I don't know what you want to happen if [txtCabinet] does not equal "Test1". I just put NA, but you would put what you want it to do (that's the else part of the statement).
The format goes like this:
iif(logical_test, [value_if_true], [value_if_false])
Aug 9 '07 #2

P: 16
Thanks, I will let you know what happens... I have successfully made calls a few times to a form, but I have never used a if statement in a query before.

Darkhat01
Aug 13 '07 #3

Scott Price
Expert 100+
P: 1,384
Thanks, I will let you know what happens... I have successfully made calls a few times to a form, but I have never used a if statement in a query before.

Darkhat01
Make sure you use both ii's in the iif statement! It means If and only If...

Regards,
Scott
Aug 13 '07 #4

P: 16
So here is what I am trying to do. If (txtCabinet) = Test 1 then I want it to show Distributed Storage, UNIX/zLinux Server, Collaboration Solutions, Web Hosting Operations, Client Computing, Network Solutions, Window Server, Enterprise Business Intelligence Services, Engineering Services, and Oracle/SQL Operations. If it does not equal Test 1 Then it will only show what is in the (txtCabinet) and it will not be any of the ones list above. I do not get any errors so somehow my logic is wrong here. It comes back with no records and it should come back with at lease 210 records.

Here is the logic I have put into the Criteria.

iif([Forms]![frmCabinetScorecard]![txtCabinet] = "Test 1", Like ("Distributed Storage" or "UNIX/zLinux Server" or "Collaboration Solutions" or "Web Hosting Operations" or "Client Computing" or "Network Solutions" or "Window Server" or "Enterprise Business Intelligence Services" or "Engineering Services" or "Oracle/SQL Operations"), Like [Forms]![frmCabinetScorecard]![txtCabinet])

Thanks,

Darkhat01
Aug 13 '07 #5

P: 16
Ohhh if I put this in I do get records back. In ("Distributed Storage","UNIX/zLinux Server","Collaboration Solutions","Web Hosting Operations","Client Computing","Network Solutions","Window Server","Enterprise Business Intelligence Services","Engineering Services","Oracle/SQL Operations")

Thanks,

darkhat01
Aug 13 '07 #6

Scott Price
Expert 100+
P: 1,384
Ohhh if I put this in I do get records back. In ("Distributed Storage","UNIX/zLinux Server","Collaboration Solutions","Web Hosting Operations","Client Computing","Network Solutions","Window Server","Enterprise Business Intelligence Services","Engineering Services","Oracle/SQL Operations")

Thanks,

darkhat01

Glad you got it working! Thanks for posting back with what worked for you also! Although I/we didn't really do much more than point you in a certain direction... LOL.

Regards,
Scott
Aug 13 '07 #7

P: 16
Actually I did not get the Iff statement to work, I still want to get that working. Any ideas? The problem is that it runs but does not come back with any records and it should come back with 210. If I run it as it is above I get 0 records. That is incorrect.
Aug 13 '07 #8

Scott Price
Expert 100+
P: 1,384
Ohhh if I put this in I do get records back. In ("Distributed Storage","UNIX/zLinux Server","Collaboration Solutions","Web Hosting Operations","Client Computing","Network Solutions","Window Server","Enterprise Business Intelligence Services","Engineering Services","Oracle/SQL Operations")

Thanks,

darkhat01
What does this mean then?

Please post the full SQL code that works/doesn't work for you!

Regards,
Scott
Aug 13 '07 #9

P: 16
Sorry for any confusion, I just found a temporary work around for a short time, but to automate this process for other users to use the DB I need the IIF statement to work. Currently I need to paste in the statement. It needs to be a one button click. This will only happen if I get the Query to work with the IIF statement.

Thanks,

Darkhat01
Aug 13 '07 #10

Scott Price
Expert 100+
P: 1,384
OK... Replacing this:

Expand|Select|Wrap|Line Numbers
  1. iif([Forms]![frmCabinetScorecard]![txtCabinet] = "Test 1", Like ("Distributed Storage" or "UNIX/zLinux Server" or "Collaboration Solutions" or "Web Hosting Operations" or "Client Computing" or "Network Solutions" or "Window Server" or "Enterprise Business Intelligence Services" or "Engineering Services" or "Oracle/SQL Operations"), Like [Forms]![frmCabinetScorecard]![txtCabinet])
With this:
Expand|Select|Wrap|Line Numbers
  1. iif([Forms]![frmCabinetScorecard]![txtCabinet] = "Test 1", In ("Distributed Storage","UNIX/zLinux Server","Collaboration Solutions","Web Hosting Operations","Client Computing","Network Solutions","Window Server","Enterprise Business Intelligence Services","Engineering Services","Oracle/SQL Operations")
  2. , [Forms]![frmCabinetScorecard]![txtCabinet])
Does or does not work?

If you are having more problems, please post back here.

And in the future, please wrap your sql statements in code tags. Select the statement, then go to the top of your reply window, click the # button to wrap the code tags around,then manually add =sql after the first CODE, resulting in this : [code=sql]

Regards,
Scott
Aug 13 '07 #11

P: 16
Does not work (IIF Statement) That I need to work
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![frmCabinetScorecard]![txtPortfolio] Text ( 255 ), [Forms]![frmCabinetScorecard]![txtCabinet] Text ( 255 );
  2.  
  3. TRANSFORM Count([CIO Scorecard All Open Risk].IDCount) AS Expr1
  4.  
  5. SELECT [CIO Scorecard All Open Risk].[Criticality of Risk] AS Expr2, Count([CIO Scorecard All Open Risk].IDCount) AS [Total Of 
  6.  
  7. IDCount]
  8.  
  9. FROM [CIO Scorecard All Open Risk]
  10.  
  11. WHERE ((([CIO Scorecard All Open Risk].Database)<>"CM Open Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO 
  12.  
  13. Scorecard All Open Risk].Database)<>"SC&A Tangents") AND (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND 
  14.  
  15. (([CIO Scorecard All Open Risk].[Line of Business]) Like [Forms]![frmCabinetScorecard]![txtPortfolio]) AND (([CIO Scorecard All 
  16.  
  17. Open Risk].Cabinet) Like [Forms]![frmCabinetScorecard]![txtCabinet])) OR ((([CIO Scorecard All Open Risk].Database)<>"CM Open 
  18.  
  19. Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO Scorecard All Open Risk].Database)<>"SC&A Tangents") AND 
  20.  
  21. (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND (([CIO Scorecard All Open Risk].[Line of Business]) Is 
  22.  
  23. Not Null) AND (([Forms]![frmCabinetScorecard]![txtPortfolio])='Enterprise')) OR ((([CIO Scorecard All Open Risk].Database)<>"CM 
  24.  
  25. Open Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO Scorecard All Open Risk].Database)<>"SC&A Tangents") 
  26.  
  27. AND (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND (([CIO Scorecard All Open Risk].[Line of Business]) 
  28.  
  29. In ("Company A-1","Company A-2","Company A-3")) AND (([CIO Scorecard All Open Risk].Cabinet) Like 
  30.  
  31. [Forms]![frmCabinetScorecard]![txtCabinet]) AND (([Forms]![frmCabinetScorecard]![txtPortfolio])='Company A')) OR ((([CIO 
  32.  
  33. Scorecard All Open Risk].Database)<>"CM Open Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO Scorecard All 
  34.  
  35. Open Risk].Database)<>"SC&A Tangents") AND (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND (([CIO 
  36.  
  37. Scorecard All Open Risk].[Line of Business]) In ("Company B-1","Company B-2","Company B-3")) AND (([CIO Scorecard All Open 
  38.  
  39. Risk].Cabinet)=IIf([Forms]![frmCabinetScorecard]![txtCabinet]="Test 1",([CIO Scorecard All Open Risk].Cabinet) Like (([CIO 
  40.  
  41. Scorecard All Open Risk].Cabinet)="Distributed Storage" Or ([CIO Scorecard All Open Risk].Cabinet)="UNIX/zLinux Server" Or 
  42.  
  43. ([CIO Scorecard All Open Risk].Cabinet)="Collaboration Solutions" Or ([CIO Scorecard All Open Risk].Cabinet)="Web Hosting 
  44.  
  45. Operations" Or ([CIO Scorecard All Open Risk].Cabinet)="Client Computing" Or ([CIO Scorecard All Open Risk].Cabinet)="Network 
  46.  
  47. Solutions" Or ([CIO Scorecard All Open Risk].Cabinet)="Window Server" Or ([CIO Scorecard All Open Risk].Cabinet)="Enterprise 
  48.  
  49. Business Intelligence Services" Or ([CIO Scorecard All Open Risk].Cabinet)="Engineering Services" Or ([CIO Scorecard All Open 
  50.  
  51. Risk].Cabinet)="Oracle/SQL Operations"),([CIO Scorecard All Open Risk].Cabinet) Like 
  52.  
  53. [Forms]![frmCabinetScorecard]![txtCabinet])) AND (([Forms]![frmCabinetScorecard]![txtPortfolio])='Company B'))
  54.  
  55. GROUP BY [CIO Scorecard All Open Risk].[Criticality of Risk]
  56.  
  57. PIVOT [CIO Scorecard All Open Risk].Database;
  58.  
  59.  

Does Work (Because of Work Around) But can not keep doing this...
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![frmCabinetScorecard]![txtPortfolio] Text ( 255 ), [Forms]![frmCabinetScorecard]![txtCabinet] Text ( 255 );
  2.  
  3. TRANSFORM Count([CIO Scorecard All Open Risk].IDCount) AS Expr1
  4.  
  5. SELECT [CIO Scorecard All Open Risk].[Criticality of Risk] AS Expr2, Count([CIO Scorecard All Open Risk].IDCount) AS [Total Of 
  6.  
  7. IDCount]
  8.  
  9. FROM [CIO Scorecard All Open Risk]
  10.  
  11. WHERE ((([CIO Scorecard All Open Risk].Database)<>"CM Open Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO 
  12.  
  13. Scorecard All Open Risk].Database)<>"SC&A Tangents") AND (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND 
  14.  
  15. (([CIO Scorecard All Open Risk].[Line of Business]) Like [Forms]![frmCabinetScorecard]![txtPortfolio]) AND (([CIO Scorecard All 
  16.  
  17. Open Risk].Cabinet) Like [Forms]![frmCabinetScorecard]![txtCabinet])) OR ((([CIO Scorecard All Open Risk].Database)<>"CM Open 
  18.  
  19. Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO Scorecard All Open Risk].Database)<>"SC&A Tangents") AND 
  20.  
  21. (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND (([CIO Scorecard All Open Risk].[Line of Business]) Is 
  22.  
  23. Not Null) AND (([Forms]![frmCabinetScorecard]![txtPortfolio])='Enterprise')) OR ((([CIO Scorecard All Open Risk].Database)<>"CM 
  24.  
  25. Open Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO Scorecard All Open Risk].Database)<>"SC&A Tangents") 
  26.  
  27. AND (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND (([CIO Scorecard All Open Risk].[Line of Business]) 
  28.  
  29. In ("Company A-1","Company A-2","Company A-3")) AND (([CIO Scorecard All Open Risk].Cabinet) Like 
  30.  
  31. [Forms]![frmCabinetScorecard]![txtCabinet]) AND (([Forms]![frmCabinetScorecard]![txtPortfolio])='Company A')) OR ((([CIO 
  32.  
  33. Scorecard All Open Risk].Database)<>"CM Open Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO Scorecard All 
  34.  
  35. Open Risk].Database)<>"SC&A Tangents") AND (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND (([CIO 
  36.  
  37. Scorecard All Open Risk].[Line of Business]) In ("Company B-1","Company B-2","Company B-3")) AND (([CIO Scorecard All Open 
  38.  
  39. Risk].Cabinet) In ("Distributed Storage","UNIX/zLinux Server","Collaboration Solutions","Web Hosting Operations","Client 
  40.  
  41. Computing","Network Solutions","Window Server","Enterprise Business Intelligence Services","Engineering Services","Oracle/SQL 
  42.  
  43. Operations")) AND (([Forms]![frmCabinetScorecard]![txtPortfolio])='Company B'))
  44.  
  45. GROUP BY [CIO Scorecard All Open Risk].[Criticality of Risk]
  46.  
  47. PIVOT [CIO Scorecard All Open Risk].Database;
  48.  
Aug 13 '07 #12

P: 16
Thanks, Scott I will try and do what you suggested. I wills tart using the code tags also.

darkhat01
Aug 13 '07 #13

Scott Price
Expert 100+
P: 1,384
Good aft! (afternoon here in S.America anyway :-)

Try changing this line:

Expand|Select|Wrap|Line Numbers
  1. ...Risk].Cabinet)=IIf([Forms]![frmCabinetScorecard]![txtCabinet]="Test 1",([CIO Scorecard All Open Risk].Cabinet) Like (([CIO...
To this:

Expand|Select|Wrap|Line Numbers
  1. ...Risk].Cabinet)=IIf([Forms]![frmCabinetScorecard]![txtCabinet]="Test 1", Like (([CIO...
I think this is line 39 or your sql that doesn't work.

Regards,
Scott
Aug 13 '07 #14

Scott Price
Expert 100+
P: 1,384
Good aft! (afternoon here in S.America anyway :-)

Try changing this line:

Expand|Select|Wrap|Line Numbers
  1. ...Risk].Cabinet)=IIf([Forms]![frmCabinetScorecard]![txtCabinet]="Test 1",([CIO Scorecard All Open Risk].Cabinet) Like (([CIO...
To this:

Expand|Select|Wrap|Line Numbers
  1. ...Risk].Cabinet)=IIf([Forms]![frmCabinetScorecard]![txtCabinet]="Test 1", Like (([CIO...
I think this is line 39 or your sql that doesn't work.

Regards,
Scott

Just looking at this a little closer, I realize that using my suggestion wouldn't fix your problem, because the inherent feature of the iif() function is that it evaluates and returns 1 true value, OR 1 false value (actually it evaluates both, even though it only returns one... that's why using it in VBA code results in performance degradation).

So the structure is iif(expression_to_be_tested, value_if_true, value_if_false). Likely, the reason it isn't working at this point is that your value_if_true is not evaluating to 1 single value, but a range of values.

I haven't personally tried nesting complex expressions in the value_if_true/false parts of the iif, but I would suspect your syntax is the problem. Try instead of my first suggestion, change the value_if_true part of your line 39 to this:

([CIO Scorecard All Open Risk].Cabinet) = Like... If that doesn't work, add another change to your control name i.e.: Forms![CIO Scorecard All Open Risk].Cabinet

Hope this helps!
Regards,
Scott
Aug 14 '07 #15

P: 16
Just looking at this a little closer, I realize that using my suggestion wouldn't fix your problem, because the inherent feature of the iif() function is that it evaluates and returns 1 true value, OR 1 false value (actually it evaluates both, even though it only returns one... that's why using it in VBA code results in performance degradation).

So the structure is iif(expression_to_be_tested, value_if_true, value_if_false). Likely, the reason it isn't working at this point is that your value_if_true is not evaluating to 1 single value, but a range of values.

I haven't personally tried nesting complex expressions in the value_if_true/false parts of the iif, but I would suspect your syntax is the problem. Try instead of my first suggestion, change the value_if_true part of your line 39 to this:

([CIO Scorecard All Open Risk].Cabinet) = Like... If that doesn't work, add another change to your control name i.e.: Forms![CIO Scorecard All Open Risk].Cabinet

Hope this helps!
Regards,
Scott
Great i am going to try it.

Thank you
Aug 16 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.