Link to home
Start Free TrialLog in
Avatar of lcha
lchaFlag for United States of America

asked on

MS access query - change values in result set using if statements, etc.

Hello,

I would like to create a make table query in access.    Before populating the results in the table, I want to change values in the results for one of the columns using "IF" statements.

e.g. If value = "XXX" then
            change value to "YYY"

Should I put the If statements in the SQL statement?  

How do I change the values?

Thanks for your help.

SELECT X, Y, Z
FROM [tbl1] INNER JOIN [tbl2] ON [tbl1].[Customer Account Number] = [tbl2].AccountNumber
WHERE ((([tbl1].[Customer Account Create Date]) Between "20110901" And "20111001"));

Open in new window

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

use the IIF function , IIf(expr, truepart, falsepart)

SELECT X, Y, Z, IIf([fieldName]="xxx","yyy",[fieldName]) as Z
From ....
Avatar of lcha

ASKER

thanks so much for your resoponse capricorn1

To clarify I need, I am actually looking for 3 different values in the column results and need to change those values to more something more understandable to the

Will the IIF function allow me to do what I need because it is limited to returning 2 different values right?

iif ([Qty] > 10, "large", "small")

Thanks!
lcha

post the conditions
Avatar of lcha

ASKER

sorry that this isn't obvious to me ... can you give an example

I need to check for 3 different values in the column results and change the values for all of them.
iif ([fieldname1]="xxx", "yyy")
iif ([fieldname1]="abc", "xxx")
iif ([fieldname1]="def", "zzz")



ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Alternatively, you can use the SWITCH function which allows multiple conditions.

i.e.
SWITCH([fieldname1]="xxx", "yyy", [fieldname1]="abc", "xxx", [fieldname1]="def", "zzz")

Open in new window

Avatar of lcha

ASKER

that worked, thank you! :)
Avatar of lcha

ASKER

thanks Thomasian, I will look into your solution as well later.