Problems writing a Query

[darkhat01]

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

[mlcampeau]

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])

[darkhat01]

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

[Scott Price]

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

[darkhat01]

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

[darkhat01]

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

[Scott Price]

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

[darkhat01]

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.

[Scott Price]

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

[darkhat01]

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

[Scott Price]

OK... Replacing this:

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:

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")
, [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

[darkhat01]

Does not work (IIF Statement) That I need to work

PARAMETERS [Forms]![frmCabinetScorecard]![txtPortfolio] Text ( 255 ), [Forms]![frmCabinetScorecard]![txtCabinet] Text ( 255 );
 
TRANSFORM Count([CIO Scorecard All Open Risk].IDCount) AS Expr1
 
SELECT [CIO Scorecard All Open Risk].[Criticality of Risk] AS Expr2, Count([CIO Scorecard All Open Risk].IDCount) AS [Total Of 
 
IDCount]
 
FROM [CIO Scorecard All Open Risk]
 
WHERE ((([CIO Scorecard All Open Risk].Database)<>"CM Open Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO 
 
Scorecard All Open Risk].Database)<>"SC&A Tangents") AND (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND 
 
(([CIO Scorecard All Open Risk].[Line of Business]) Like [Forms]![frmCabinetScorecard]![txtPortfolio]) AND (([CIO Scorecard All 
 
Open Risk].Cabinet) Like [Forms]![frmCabinetScorecard]![txtCabinet])) OR ((([CIO Scorecard All Open Risk].Database)<>"CM Open 
 
Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO Scorecard All Open Risk].Database)<>"SC&A Tangents") AND 
 
(([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND (([CIO Scorecard All Open Risk].[Line of Business]) Is 
 
Not Null) AND (([Forms]![frmCabinetScorecard]![txtPortfolio])='Enterprise')) OR ((([CIO Scorecard All Open Risk].Database)<>"CM 
 
Open Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO Scorecard All Open Risk].Database)<>"SC&A Tangents") 
 
AND (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND (([CIO Scorecard All Open Risk].[Line of Business]) 
 
In ("Company A-1","Company A-2","Company A-3")) AND (([CIO Scorecard All Open Risk].Cabinet) Like 
 
[Forms]![frmCabinetScorecard]![txtCabinet]) AND (([Forms]![frmCabinetScorecard]![txtPortfolio])='Company A')) OR ((([CIO 
 
Scorecard All Open Risk].Database)<>"CM Open Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO Scorecard All 
 
Open Risk].Database)<>"SC&A Tangents") AND (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND (([CIO 
 
Scorecard All Open Risk].[Line of Business]) In ("Company B-1","Company B-2","Company B-3")) AND (([CIO Scorecard All Open 
 
Risk].Cabinet)=IIf([Forms]![frmCabinetScorecard]![txtCabinet]="Test 1",([CIO Scorecard All Open Risk].Cabinet) Like (([CIO 
 
Scorecard All Open Risk].Cabinet)="Distributed Storage" Or ([CIO Scorecard All Open Risk].Cabinet)="UNIX/zLinux Server" Or 
 
([CIO Scorecard All Open Risk].Cabinet)="Collaboration Solutions" Or ([CIO Scorecard All Open Risk].Cabinet)="Web Hosting 
 
Operations" Or ([CIO Scorecard All Open Risk].Cabinet)="Client Computing" Or ([CIO Scorecard All Open Risk].Cabinet)="Network 
 
Solutions" Or ([CIO Scorecard All Open Risk].Cabinet)="Window Server" Or ([CIO Scorecard All Open Risk].Cabinet)="Enterprise 
 
Business Intelligence Services" Or ([CIO Scorecard All Open Risk].Cabinet)="Engineering Services" Or ([CIO Scorecard All Open 
 
Risk].Cabinet)="Oracle/SQL Operations"),([CIO Scorecard All Open Risk].Cabinet) Like 
 
[Forms]![frmCabinetScorecard]![txtCabinet])) AND (([Forms]![frmCabinetScorecard]![txtPortfolio])='Company B'))
 
GROUP BY [CIO Scorecard All Open Risk].[Criticality of Risk]
 
PIVOT [CIO Scorecard All Open Risk].Database;
 
 

Does Work (Because of Work Around) But can not keep doing this...

PARAMETERS [Forms]![frmCabinetScorecard]![txtPortfolio] Text ( 255 ), [Forms]![frmCabinetScorecard]![txtCabinet] Text ( 255 );
 
TRANSFORM Count([CIO Scorecard All Open Risk].IDCount) AS Expr1
 
SELECT [CIO Scorecard All Open Risk].[Criticality of Risk] AS Expr2, Count([CIO Scorecard All Open Risk].IDCount) AS [Total Of 
 
IDCount]
 
FROM [CIO Scorecard All Open Risk]
 
WHERE ((([CIO Scorecard All Open Risk].Database)<>"CM Open Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO 
 
Scorecard All Open Risk].Database)<>"SC&A Tangents") AND (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND 
 
(([CIO Scorecard All Open Risk].[Line of Business]) Like [Forms]![frmCabinetScorecard]![txtPortfolio]) AND (([CIO Scorecard All 
 
Open Risk].Cabinet) Like [Forms]![frmCabinetScorecard]![txtCabinet])) OR ((([CIO Scorecard All Open Risk].Database)<>"CM Open 
 
Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO Scorecard All Open Risk].Database)<>"SC&A Tangents") AND 
 
(([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND (([CIO Scorecard All Open Risk].[Line of Business]) Is 
 
Not Null) AND (([Forms]![frmCabinetScorecard]![txtPortfolio])='Enterprise')) OR ((([CIO Scorecard All Open Risk].Database)<>"CM 
 
Open Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO Scorecard All Open Risk].Database)<>"SC&A Tangents") 
 
AND (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND (([CIO Scorecard All Open Risk].[Line of Business]) 
 
In ("Company A-1","Company A-2","Company A-3")) AND (([CIO Scorecard All Open Risk].Cabinet) Like 
 
[Forms]![frmCabinetScorecard]![txtCabinet]) AND (([Forms]![frmCabinetScorecard]![txtPortfolio])='Company A')) OR ((([CIO 
 
Scorecard All Open Risk].Database)<>"CM Open Risk" And ([CIO Scorecard All Open Risk].Database)<>"ASOF" And ([CIO Scorecard All 
 
Open Risk].Database)<>"SC&A Tangents") AND (([CIO Scorecard All Open Risk].[Risk Assumed WO Mitigation])<>-1) AND (([CIO 
 
Scorecard All Open Risk].[Line of Business]) In ("Company B-1","Company B-2","Company B-3")) AND (([CIO Scorecard All Open 
 
Risk].Cabinet) 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")) AND (([Forms]![frmCabinetScorecard]![txtPortfolio])='Company B'))
 
GROUP BY [CIO Scorecard All Open Risk].[Criticality of Risk]
 
PIVOT [CIO Scorecard All Open Risk].Database;
 

[darkhat01]

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

darkhat01

[Scott Price]

Good aft! (afternoon here in S.America anyway :-)

Try changing this line:

...Risk].Cabinet)=IIf([Forms]![frmCabinetScorecard]![txtCabinet]="Test 1",([CIO Scorecard All Open Risk].Cabinet) Like (([CIO...

To this:

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

[Scott Price]

Good aft! (afternoon here in S.America anyway :-)

Try changing this line:

...Risk].Cabinet)=IIf([Forms]![frmCabinetScorecard]![txtCabinet]="Test 1",([CIO Scorecard All Open Risk].Cabinet) Like (([CIO...

To this:

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

[darkhat01]

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