Having Trouble Adding IIF to Select Statement

[BrianFawcett]

I am creating a report and and trying to add an IIF statement to my SELECT, but cannot get the syntzx correct. Can someone help me add the IIF below to the SELECT below. Thanks.

IIF [Order Type] = "ACE" or "Supplies" or "Rebill"

SELECT Count(REPORTTempTable.[InHouse Ref]) AS [CountOfInHouse Ref1]

 

[Alansidman]

Not sure where you are trying to do this.

Do you want these values to be criteria for the field [ORDER TYPE] or are you trying to create an expression in a new field? Tell us what you are trying to do. Either of these will mean a different SQL statement.

Alan

 

[BrianFawcett]

I am trying to get a count on the field [InHouse Ref] in the instances where the field [Order Type] has a value of "ACE" or "Supplies" or "Rebill". Does this help?

 

[plog]

You don't need an IIF statement, you need a WHERE clause:

SELECT Count([InHouse Ref]) AS CountOfInHouseRef1 FROM  REPORTTempTable WHERE [ORDER Type] = "ACE" OR [ORDER Type] = "Supplies" OR [ORDER Type] = "Rebill";

 

[BrianFawcett]

I tried adding the WHERE statement as part of the SELECT, but it is not working. I believe I cannot place it there. Here is my code.

SELECT REPORTTempTable.[QA Date Approved], REPORTTempTable.[QA Rep], Count(REPORTTempTable.[InHouse Ref]) AS [CountOfInHouse Ref], Count(REPORTTempTable.[InHouse Ref]) AS [CountOfInHouse Ref1] WHERE [REPORTTempTable].[ORDER Type] = "ACE" OR [REPORTTempTable].[ORDER Type] = "Supplies" OR [REPORTTempTable].[ORDER Type] = "Rebill"
FROM REPORTTempTable
GROUP BY REPORTTempTable.[QA Date Approved], REPORTTempTable.[QA Rep]
HAVING (((REPORTTempTable.[QA Date Approved]) Between [Forms]![Reporting Form].[DateFrom] And [Forms]![Reporting Form].[DateTo]));

 

[vbaInet]

You put the WHERE part plog gave to you in the wrong place.

Some amendments + introducing the IN clause:

SELECT [QA Date Approved], [QA Rep], Count([InHouse Ref]) AS [CountOfInHouse Ref], Count([InHouse Ref]) AS [CountOfInHouse Ref1] 
FROM REPORTTempTable 
WHERE [ORDER Type] IN ('ACE', 'Supplies', 'Rebill') 
    AND [QA Date Approved] BETWEEN [Forms]![Reporting Form]![DateFrom] And [Forms]![Reporting Form]![DateTo] 
GROUP BY [QA Date Approved], [QA Rep];