populate a field based off a state selection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to populate a field using date based off a state...ex.

if State = Texas, then [part a]![Sale date] or if state = Utah, then [Part
a]![r-c-r date] and so on

What is the best way to accomplish this..

I was trying =iif[state]="Texas", "[sale date]" - but I get a #Name error
 
dchristo said:
I want to populate a field using date based off a state...ex.

if State = Texas, then [part a]![Sale date] or if state = Utah, then [Part
a]![r-c-r date] and so on

What is the best way to accomplish this..

I was trying =iif[state]="Texas", "[sale date]" - but I get a #Name error
Click to expand...


Since this is a forms forum, I will assume that you are
trying to use a text box control to display the date. If
that's correct and if the form is named [part a], then try
this:

=IIf(state="Texas", [sale date], IIf(state="Utah", [r-c-r
date], Null))

It will probably be easier to understand to use:

=Switch(state="Texas", [sale date], state="Utah", [r-c-r
date], True, Null)
 
That works great thank you, but what if I have Texas, Mississippi, and Ohio
using the sale date and Utah, Hawaii and Oregon using the r-c-r date

Marshall Barton said:
dchristo said:
I want to populate a field using date based off a state...ex.

if State = Texas, then [part a]![Sale date] or if state = Utah, then [Part
a]![r-c-r date] and so on

What is the best way to accomplish this..

I was trying =iif[state]="Texas", "[sale date]" - but I get a #Name error
Click to expand...


Since this is a forms forum, I will assume that you are
trying to use a text box control to display the date. If
that's correct and if the form is named [part a], then try
this:

=IIf(state="Texas", [sale date], IIf(state="Utah", [r-c-r
date], Null))

It will probably be easier to understand to use:

=Switch(state="Texas", [sale date], state="Utah", [r-c-r
date], True, Null)
Click to expand...
 
This is getting clumsy. You should consider creating a
table states with a field to indicate the type of date.
E.g.
State DateType
Texas S
Mississippi S
Ohio S
Utah R
. . .
Then you can join that table in the query and use a simpler
expression:
=Switch(DateType = "S", [sale date], DateType = "R", [r-c-r
date], True, Null)

This way you can change the DateType for a state without
having to hunt through your code.queries,etc to find the
places where it is used.

OTOH, if you happy with the embedded state names, you can
use the IN operator:

=Switch(state IN("Texas","Mississippi","Ohio"), [sale
date], state IN("Utah","Hawaii","Oregon"), [r-c-r date],
True, Null)
--
Marsh
MVP [MS Access]


That works great thank you, but what if I have Texas, Mississippi, and Ohio
using the sale date and Utah, Hawaii and Oregon using the r-c-r date

dchristo said:
I want to populate a field using date based off a state...ex.

if State = Texas, then [part a]![Sale date] or if state = Utah, then [Part
a]![r-c-r date] and so on

What is the best way to accomplish this..

I was trying =iif[state]="Texas", "[sale date]" - but I get a #Name error
Click to expand...
Click to expand...
Marshall Barton said:
Since this is a forms forum, I will assume that you are
trying to use a text box control to display the date. If
that's correct and if the form is named [part a], then try
this:

=IIf(state="Texas", [sale date], IIf(state="Utah", [r-c-r
date], Null))

It will probably be easier to understand to use:

=Switch(state="Texas", [sale date], state="Utah", [r-c-r
date], True, Null)
Click to expand...
Click to expand...
 
Back
Top