IF - THEN Statement

F

fgwiii

Hello,

I am attemping to place the statement below into a query
but I am unable to do so. Can you please tell be if this
is possible or is there a better way to getting this done?

if [Status_report]![State] = "MA" then "East Region"
else if [Status_report]![State] = "ME" then "East Region"
else if [Status_report]![State] = "NH" then "East Region"
else if [Status_report]![State] = "RI" then "East Region"
else if [Status_report]![State] = "VT" then "East Region"
else if [Status_report]![State] = "CT" then "East Region"
else if [Status_report]![State] = "NY" then "East Region"
else if [Status_report]![State] = "NJ" then "East Region"
else if [Status_report]![State] = "DE" then "East Region"
else if [Status_report]![State] = "PA" then "East Region"
else if [Status_report]![State] = "DC" then "East Region"
else if [Status_report]![State] = "MD" then "East Region"
else if [Status_report]![State] = "VA" then "East Region"
else if [Status_report]![State] = "NC" then "East Region"
else if [Status_report]![State] = "SC" then "East Region"
else if [Status_report]![State] = "MI" then "Central
Region"
else if [Status_report]![State] = "OH" then "Central
Region"
else if [Status_report]![State] = "TX" then "Central
Region"
else if [Status_report]![State] = "AL" then "Central
Region"
else if [Status_report]![State] = "AR" then "Central
Region"
else if [Status_report]![State] = "LA" then "Central
Region"
else if [Status_report]![State] = "MS" then "Central
Region"
else if [Status_report]![State] = "IN" then "Central
Region"
else if [Status_report]![State] = "KY" then "Central
Region"
else if [Status_report]![State] = "WV" then "Central
Region"
else if [Status_report]![State] = "KS" then "Central
Region"
else if [Status_report]![State] = "MO" then "Central
Region"
else if [Status_report]![State] = "NE" then "Central
Region"
else if [Status_report]![State] = "OK" then "Central
Region"
else if [Status_report]![State] = "GA" then "Central
Region"
else if [Status_report]![State] = "TN" then "Central
Region"
else if [Status_report]![State] = "FL" then "Central
Region"
else if [Status_report]![State] = "PR" then "Central
Region"
else if [Status_report]![State] = "VI" then "Central
Region"
else "West Region"

Thanks for your help!

Fred
 
D

Duane Hookom

I would add a [Region] field to your table of unique states. The huge
expression you are working with is a nightmare. What would happen if the PHB
(Pointy Hair Boss) says "We are moving AL, FL, TX, etc to a new 'South
Region'"?

This kinda of logic must be managed in data, not expressions. I wouldn't
accept anything else and I hope you wouldn't either...

--
Duane Hookom
MS Access MVP


fgwiii said:
Hello,

I am attemping to place the statement below into a query
but I am unable to do so. Can you please tell be if this
is possible or is there a better way to getting this done?

if [Status_report]![State] = "MA" then "East Region"
else if [Status_report]![State] = "ME" then "East Region"
else if [Status_report]![State] = "NH" then "East Region"
else if [Status_report]![State] = "RI" then "East Region"
else if [Status_report]![State] = "VT" then "East Region"
else if [Status_report]![State] = "CT" then "East Region"
else if [Status_report]![State] = "NY" then "East Region"
else if [Status_report]![State] = "NJ" then "East Region"
else if [Status_report]![State] = "DE" then "East Region"
else if [Status_report]![State] = "PA" then "East Region"
else if [Status_report]![State] = "DC" then "East Region"
else if [Status_report]![State] = "MD" then "East Region"
else if [Status_report]![State] = "VA" then "East Region"
else if [Status_report]![State] = "NC" then "East Region"
else if [Status_report]![State] = "SC" then "East Region"
else if [Status_report]![State] = "MI" then "Central
Region"
else if [Status_report]![State] = "OH" then "Central
Region"
else if [Status_report]![State] = "TX" then "Central
Region"
else if [Status_report]![State] = "AL" then "Central
Region"
else if [Status_report]![State] = "AR" then "Central
Region"
else if [Status_report]![State] = "LA" then "Central
Region"
else if [Status_report]![State] = "MS" then "Central
Region"
else if [Status_report]![State] = "IN" then "Central
Region"
else if [Status_report]![State] = "KY" then "Central
Region"
else if [Status_report]![State] = "WV" then "Central
Region"
else if [Status_report]![State] = "KS" then "Central
Region"
else if [Status_report]![State] = "MO" then "Central
Region"
else if [Status_report]![State] = "NE" then "Central
Region"
else if [Status_report]![State] = "OK" then "Central
Region"
else if [Status_report]![State] = "GA" then "Central
Region"
else if [Status_report]![State] = "TN" then "Central
Region"
else if [Status_report]![State] = "FL" then "Central
Region"
else if [Status_report]![State] = "PR" then "Central
Region"
else if [Status_report]![State] = "VI" then "Central
Region"
else "West Region"

Thanks for your help!

Fred
Click to expand...
 
F

fgwiii

If I knew how to get the data there - I would have done so
already. If you have a moment, I could really use a
little "how to".

Fred
-----Original Message-----
I would add a [Region] field to your table of unique states. The huge
expression you are working with is a nightmare. What would happen if the PHB
(Pointy Hair Boss) says "We are moving AL, FL, TX, etc to a new 'South
Region'"?

This kinda of logic must be managed in data, not expressions. I wouldn't
accept anything else and I hope you wouldn't either...

--
Duane Hookom
MS Access MVP


fgwiii said:
Hello,

I am attemping to place the statement below into a query
but I am unable to do so. Can you please tell be if this
is possible or is there a better way to getting this done?

if [Status_report]![State] = "MA" then "East Region"
else if [Status_report]![State] = "ME" then "East Region"
else if [Status_report]![State] = "NH" then "East Region"
else if [Status_report]![State] = "RI" then "East Region"
else if [Status_report]![State] = "VT" then "East Region"
else if [Status_report]![State] = "CT" then "East Region"
else if [Status_report]![State] = "NY" then "East Region"
else if [Status_report]![State] = "NJ" then "East Region"
else if [Status_report]![State] = "DE" then "East Region"
else if [Status_report]![State] = "PA" then "East Region"
else if [Status_report]![State] = "DC" then "East Region"
else if [Status_report]![State] = "MD" then "East Region"
else if [Status_report]![State] = "VA" then "East Region"
else if [Status_report]![State] = "NC" then "East Region"
else if [Status_report]![State] = "SC" then "East Region"
else if [Status_report]![State] = "MI" then "Central
Region"
else if [Status_report]![State] = "OH" then "Central
Region"
else if [Status_report]![State] = "TX" then "Central
Region"
else if [Status_report]![State] = "AL" then "Central
Region"
else if [Status_report]![State] = "AR" then "Central
Region"
else if [Status_report]![State] = "LA" then "Central
Region"
else if [Status_report]![State] = "MS" then "Central
Region"
else if [Status_report]![State] = "IN" then "Central
Region"
else if [Status_report]![State] = "KY" then "Central
Region"
else if [Status_report]![State] = "WV" then "Central
Region"
else if [Status_report]![State] = "KS" then "Central
Region"
else if [Status_report]![State] = "MO" then "Central
Region"
else if [Status_report]![State] = "NE" then "Central
Region"
else if [Status_report]![State] = "OK" then "Central
Region"
else if [Status_report]![State] = "GA" then "Central
Region"
else if [Status_report]![State] = "TN" then "Central
Region"
else if [Status_report]![State] = "FL" then "Central
Region"
else if [Status_report]![State] = "PR" then "Central
Region"
else if [Status_report]![State] = "VI" then "Central
Region"
else "West Region"

Thanks for your help!

Fred
Click to expand...


.
Click to expand...
 
D

Duane Hookom

Do you have a table of states? If not, then create one and add a field named
Region. The query you are creating should then include the table of states
with the state fields joined. You could then drop the Region field into the
grid for display.

--
Duane Hookom
MS Access MVP


fgwiii said:
If I knew how to get the data there - I would have done so
already. If you have a moment, I could really use a
little "how to".

Fred
-----Original Message-----
I would add a [Region] field to your table of unique states. The huge
expression you are working with is a nightmare. What would happen if the PHB
(Pointy Hair Boss) says "We are moving AL, FL, TX, etc to a new 'South
Region'"?

This kinda of logic must be managed in data, not expressions. I wouldn't
accept anything else and I hope you wouldn't either...

--
Duane Hookom
MS Access MVP


fgwiii said:
Hello,

I am attemping to place the statement below into a query
but I am unable to do so. Can you please tell be if this
is possible or is there a better way to getting this done?

if [Status_report]![State] = "MA" then "East Region"
else if [Status_report]![State] = "ME" then "East Region"
else if [Status_report]![State] = "NH" then "East Region"
else if [Status_report]![State] = "RI" then "East Region"
else if [Status_report]![State] = "VT" then "East Region"
else if [Status_report]![State] = "CT" then "East Region"
else if [Status_report]![State] = "NY" then "East Region"
else if [Status_report]![State] = "NJ" then "East Region"
else if [Status_report]![State] = "DE" then "East Region"
else if [Status_report]![State] = "PA" then "East Region"
else if [Status_report]![State] = "DC" then "East Region"
else if [Status_report]![State] = "MD" then "East Region"
else if [Status_report]![State] = "VA" then "East Region"
else if [Status_report]![State] = "NC" then "East Region"
else if [Status_report]![State] = "SC" then "East Region"
else if [Status_report]![State] = "MI" then "Central
Region"
else if [Status_report]![State] = "OH" then "Central
Region"
else if [Status_report]![State] = "TX" then "Central
Region"
else if [Status_report]![State] = "AL" then "Central
Region"
else if [Status_report]![State] = "AR" then "Central
Region"
else if [Status_report]![State] = "LA" then "Central
Region"
else if [Status_report]![State] = "MS" then "Central
Region"
else if [Status_report]![State] = "IN" then "Central
Region"
else if [Status_report]![State] = "KY" then "Central
Region"
else if [Status_report]![State] = "WV" then "Central
Region"
else if [Status_report]![State] = "KS" then "Central
Region"
else if [Status_report]![State] = "MO" then "Central
Region"
else if [Status_report]![State] = "NE" then "Central
Region"
else if [Status_report]![State] = "OK" then "Central
Region"
else if [Status_report]![State] = "GA" then "Central
Region"
else if [Status_report]![State] = "TN" then "Central
Region"
else if [Status_report]![State] = "FL" then "Central
Region"
else if [Status_report]![State] = "PR" then "Central
Region"
else if [Status_report]![State] = "VI" then "Central
Region"
else "West Region"

Thanks for your help!

Fred
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Interesting DataSet/DataTable question 3
If...Then...Else Statement Help 13
IF Statement???? 4
converting a for next loop into a for each loop 2
Array.BinarySearch ALWAYS NEGETIVE 3
Formatting Data being extracted from SQL D/B 1
copy a range in an IF statement 5
Automation of report, daily, monthly then yearly as per need 1

Top