how to create a multi-part IF statement

[Merey]

In an Access database form I have a drop down field that has three possible
choices. I want to create a calculated field with an IF statement that says
if choice#1 display "CHOICE#1", if choice#2 display "CHOICE#2", if choice#3
display "CHOICE#3" otherwise leave this space empty.

I can get one of them to show but not if multiple. Can anyone help me write
the If statement the right way?

 

[Duane Hookom]

Can you try explain this again? Drop down (combo boxes) belong on forms, not
in tables. Are you expecting to be able to make multiple selections?

 

[Tim Ferguson]

I want to create a calculated field with an IF statement that says
if choice#1 display "CHOICE#1", if choice#2 display "CHOICE#2", if
choice#3 display "CHOICE#3" otherwise leave this space empty.

if you have to do it in a single expression, you can nest IIf clauses
sic:

.value = iif(cboChoice.Value = "Choice#1", "Once chosen", _
iif(cboChoice.Value = "Choice#2", "Twice chosen", _
iif(cboChoice.Value = "Choice#3", "Thrice chosen", _
"")))

it's clearer to use a SELECT CASE construct:

select case cboChoice.Value
case "Choice#1" : .Value = "Once chosen"
case "Choice#2" : .Value = "Twice chosen"
case "Choice#3" : .Value = "Thrice chosen"
case else : .Value = ""
end select

or, if you can get the data sorted properly, you can do a direct
computation:

.Value = Trim$(Mid$( _
"Once Twice Thrice ", cboChoice.ListIndex*6, 6 _
))


*-----

Mind you, I am a bit worried about this question in a "Tables & DB
Design" group, especially when you use a phrase like "calculated field".
Just for reassurance, do tell me that you are laying out controls on a
form, not planning columns in a table?

Hope it helps


Tim F

 

[Merey]

Tim,

Thank you I will try this & let you know if it works for me. I'm very new
to postings and I'm guessing I chose the wrong area. It will be put into a
form not a table. I saw design and as I'm designing the databse I figured
that's where I want to be.

Merey

 

[Merey]

The calculated field will be in a form not a table. Guessing I made the wrong
choice of where to post my question. sorry. I have something to try and will
be back to let you know if it works for me or not. Thanks-Merey

 

[Merey]

Tim - I did the nesting and it worked just like it should. Bless your heart!
I'm pretty much teaching myself and I know just enough to be dangerous. I
have a hard time figuring out where to find the answers I need. I believe
I've just found my resource. This was fast and perfect.

Thanks again -Merey

 

[Tim Ferguson]

It will be put into a
form not a table. I saw design and as I'm designing the databse I figured
that's where I want to be.

Don't worry about posting to this group: there is a lot of overlap. The
dig was aimed more at the possibility that you were using one of
Access's awful "Lookup Field" types, which continue to cause huge
amounts of misery. Using the UI to translate data for presentation is
completely appropriate.

Glad it worked out


Tim F