IF Then Else Statement

[Guest]

New Column Name: CostFactor1. Statement Criteria: If [vendor code] field is
667 then the value will be 667, else if vendor code is 1897 then value is
1897, else (if none of those two are met) then result is a combination of
[Manufacturer Code] & [Category] fields.

I've tried the two following syntax but none worked. I am entering the
expression into the "Field" box in Query design View:

One: Get an Invalid Syntax message and does not let me save the statement
CostFactor1: If [Vendor Code] = "667" Then “667"] ElseIf [vendor code] =
"1897" Then ["1897"] [Else [manufacturer code] & [category]] End If

Two: Get Undefined Function If in Expression message when attempting to run
the query
Cost Factor1: If([Vendor Code]=[â€667â€],[â€667â€],ElseIf([vendor
Code]=[â€1897â€],[â€1897â€],Else([Manufacturer Code] & [Category])))

What am I doing wrong?

Thanks,

 

[Guest]

Hi h,

In query, it is call "IIF".

Try the following:
SELECT IIf([Vendor Code]="667","667",IIf([vendor
code]="1897","1897",[manufacturer code] & [category])) AS CostFactor1;

 

[Guest]

This worked!! Thanks so much, I really aprpeciate it.

Hi h,

In query, it is call "IIF".

Try the following:
SELECT IIf([Vendor Code]="667","667",IIf([vendor
code]="1897","1897",[manufacturer code] & [category])) AS CostFactor1;

New Column Name: CostFactor1. Statement Criteria: If [vendor code] field is
667 then the value will be 667, else if vendor code is 1897 then value is
1897, else (if none of those two are met) then result is a combination of
[Manufacturer Code] & [Category] fields.

I've tried the two following syntax but none worked. I am entering the
expression into the "Field" box in Query design View:

One: Get an Invalid Syntax message and does not let me save the statement
CostFactor1: If [Vendor Code] = "667" Then “667"] ElseIf [vendor code] =
"1897" Then ["1897"] [Else [manufacturer code] & [category]] End If

Two: Get Undefined Function If in Expression message when attempting to run
the query
Cost Factor1: If([Vendor Code]=[â€667â€],[â€667â€],ElseIf([vendor
Code]=[â€1897â€],[â€1897â€],Else([Manufacturer Code] & [Category])))

What am I doing wrong?

Thanks,