How do i format this syntax . . .

[crisenr]

This is my current SQL Statement:

SELECT [qryGroupByAMCount].[TestCode], [qryGroupByAMCount].[Price], [qryGroupByAMCount].[HID], [qryGroupByAMCount].[Month], [qryGroupByAMCount].[CountOfAutoNumber], IIf([TESTCODE]="PTCGCD",[CountOfAutonumber]*2,IIf([TESTCODE]="LSHABC",[CountofAutonumber]*4,IIf([TESTCODE]="HPVPNL",[CountOfAutonumber]*2,IIf([TESTCODE]="TOXOAB",[CountofAutonumber]*2,[CountofAutonumber])))) AS Extended
FROM qryGroupByAMCount;

I wanted to add some SQL which will filter out some entries in this format:

<> "TestCodeName1" or <> "TestCodeName2" <> "TestCodeName3"...

How do i incorporate this statement into mine?

 

[Jon K]

Assuming [TestCode] is the field to which you want to apply the filters and that [TestCode] is a text field (i.e. not a numeric field), try the Where Clause below using NOT IN:-

SELECT [qryGroupByAMCount].[TestCode],
[qryGroupByAMCount].[Price], [qryGroupByAMCount].[HID], [qryGroupByAMCount].[Month], [qryGroupByAMCount].[CountOfAutoNumber],
IIf([TESTCODE]="PTCGCD",[CountOfAutonumber]*2,
IIf([TESTCODE]="LSHABC",[CountofAutonumber]*4,
IIf([TESTCODE]="HPVPNL",[CountOfAutonumber]*2,
IIf([TESTCODE]="TOXOAB",[CountofAutonumber]*2,[CountofAutonumber])))) AS Extended
FROM qryGroupByAMCount
Where [TestCode] NOT IN ("TestCodeName1", "TestCodeName2", "TestCodeName3");


You can also simplify the IIF expression by using IN:-

IIf([TESTCODE]="LSHABC",[CountofAutonumber]*4,
IIf([TESTCODE] IN ("PTCGCD","HPVPNL","TOXOAB"),[CountOfAutonumber]*2, [CountofAutonumber])) AS Extended
.