Using If or Iif to determine a value

  • Thread starter Thread starter CW
  • Start date Start date
C

CW

I have a query that calculates various costs. There's one field that has 3
possible values, and depending on what it is the value (cost) can be shown in
another field.
It's along these lines:
If [Field1] = US then [Field2] = 1.00; If [Field1] = Europe then [Field2] =
2.00; If [Field1] = Rest of World then [Field2] = 3.50
Could somebody give me the correct syntax, please?
Many thanks
CW
 
While you can do this with an IIF() statement (see Access HELP for exact
syntax), what about the idea of using the lookup table that holds "US",
"Europe" and other values? You do have a "lookup table" with these values,
right? Otherwise, someone could type in "USA" or "United States" and your
IIF() statement would fail to find those.

With a lookup table in place, you can add a column of related values, then
use a query to display the related value, rather than setting it in a table.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Great idea, Jeff - I'll do it that way instead
Many thanks
CW

Jeff Boyce said:
While you can do this with an IIF() statement (see Access HELP for exact
syntax), what about the idea of using the lookup table that holds "US",
"Europe" and other values? You do have a "lookup table" with these values,
right? Otherwise, someone could type in "USA" or "United States" and your
IIF() statement would fail to find those.

With a lookup table in place, you can add a column of related values, then
use a query to display the related value, rather than setting it in a table.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

CW said:
I have a query that calculates various costs. There's one field that has 3
possible values, and depending on what it is the value (cost) can be shown
in
another field.
It's along these lines:
If [Field1] = US then [Field2] = 1.00; If [Field1] = Europe then [Field2]
=
2.00; If [Field1] = Rest of World then [Field2] = 3.50
Could somebody give me the correct syntax, please?
Many thanks
CW
Click to expand...


.
Click to expand...
 
You can also use the Switch function, if you're sure that your input will be
restricted to the values you expect.

Switch([Field1] = "US" ; 1.00; [Field1] = "Europe" ; 2.00; [Field1] = "Rest
of World" ; 3.50)

Pete
 
Back
Top