If-Then-Else help needed in my Query

[gbs01]

I have the following field in my tblTest

SS7
---
2
2
2
1
1
1

Heres what I need for my query field:
-------------------------------------
If SS7 = 2 then make OrigOCN = 0000 and TermOCN = 9915
Else make OrigOCN = 9915 and TermOCN = 0000

Note: OrigOCN and TermOCN do not exist in my table or query.

Thanks in advance for your help!
jalexander

 

[traingamer]

If I understand correctly, try:

Select SS7, iif([ss7]=2, '0000', '9915') as OrigOCN, iif([ss7]=2, '9915', '0000') as TermOCN 
From tblTest;

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[gbs01]

Thanks for the help!

How do I insert that code into my Query grid?

I already have a query built & need your code to work with it.
Here's the sql view of what I have currently:

SELECT 110101 AS 1thru6, [14&16IN0205Template].CallArrival, Left([CallArrival],InStr([CallArrival],".")-1) AS aCADate, Format([aCADate],"yymmdd") AS CADate, CDate(Left([CallCompletion],InStr([CallCompletion],'.')-1))-CDate(Left([CallArrival],InStr([CallArrival],'.')-1)) AS ElapsedTime, Format([aCADate],"hhnnss") AS ConnectTime, "00" AS 13thru14, [14&16IN0205Template].Calling, Right("0000000000" & [Calling],10) AS CallingFrom, "00000" AS 25thru29, [14&16IN0205Template].Called, Right("0000000000" & [Called],10) AS CalledTo, [14&16IN0205Template].SS7_CustRoute, IIf([SS7_CustRoute]="2","0000","9915") AS Expr1, "00000" AS 41thru45, Format([CICIn],"0000") AS CICin4, [14&16IN0205Template].CICIn, "00000" AS 50thru54, [14&16IN0205Template].CallCompletion, Format([ElapsedTime],"hhnnss") AS BillableTime, "0" AS 67, "00000000000000000000000000000000000000000000000000000000000000000000000000000" AS 68thru145, "D" AS 146, "00000000000000000000" AS 147thru166, Format([CICOut],"0000") AS CICOut4, [14&16IN0205Template].CICOut, "00000000000" AS 171thru181, "0000000000000000000000000" AS 186thru210
FROM [14&16IN0205Template];

 

[traingamer]

Looks like you already have part of it in there

[COLOR=red]IIf([SS7_CustRoute]="2","0000","9915") AS Expr1[/color],"00000" AS 41thru45, Format([CICIn],"0000") AS CICin4,

but, in any case, you'd just add it in the query like

[COLOR=red]IIf([SS7_CustRoute]="2","0000","9915") AS Expr1[/color],"00000" AS 41thru45, Format([CICIn],"0000") AS CICin4,[COLOR=blue]IIf([SS7_CustRoute]="2","9915","0000") AS TermOCN[/color],

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[gbs01]

ok,....but is your code taking in account that if the SS7 field is 1 then switch the 2 values for OrigOCN & TermOCN?


Your origianl post looked exactly right, but it gives me the same values in both new fields.

Thanks again!
jalexander

 

[traingamer]

IIf([SS7_CustRoute]="2","9915","0000") AS TermOCN

simply says - if the value of [SS7_CustRoute]="2" then use "9915" as TermOCN otherwise use "0000" as TermOCN.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[gbs01]

OK, I got it! It works fine!

Thanks for the tip. I now know how to assign the new value to a new field. That was the part I was unsure of.

Thanks Greg.

jalexander