Select Case Statement

[Brigitte P]

I forgot how to do this. I simply want the following. If Unit = "1W" then
say "1 West" or If Unit = "2 W" then "2 West" or If Unit '= "GF" then
"Groundfloor". I know it is simple, but I forgot how to write the
Select Case Unit
Case "1W" ...........and then I'm lost.
It can either occur in the underlying query (and I have no idea how to do
this) or on the report maybe in the On Format property?
Please help.
Brigitte P

 

[Rick Brandt]

I forgot how to do this. I simply want the following. If Unit = "1W" then
say "1 West" or If Unit = "2 W" then "2 West" or If Unit '= "GF" then
"Groundfloor". I know it is simple, but I forgot how to write the
Select Case Unit
Case "1W" ...........and then I'm lost.
It can either occur in the underlying query (and I have no idea how to do
this) or on the report maybe in the On Format property?
Please help.
Brigitte P

Select Case can only be used in code, not a query or control expression. In a
Report's Format property you would use something like...

Select Case Me![Unit]
Case "1 W"
Me!SomeTextBox = "1 West"
Case "2 W"
Me!SomeTextBox = "2 West"
Case "GF"
Me!SomeTextBox = "Ground Floor"
End Select

In a query you could use IIf() or the Switch() function.

 

[Brigitte P]

Thanks Rick. Yes, I can do it in code. Do I have to declare the new variable
e.g., Dim NewUnit as Integer or something like that? I'll try your solution
right now.
Brigitte P.

I forgot how to do this. I simply want the following. If Unit = "1W" then
say "1 West" or If Unit = "2 W" then "2 West" or If Unit '= "GF" then
"Groundfloor". I know it is simple, but I forgot how to write the
Select Case Unit
Case "1W" ...........and then I'm lost.
It can either occur in the underlying query (and I have no idea how to do
this) or on the report maybe in the On Format property?
Please help.
Brigitte P

Select Case can only be used in code, not a query or control expression. In a
Report's Format property you would use something like...

Select Case Me![Unit]
Case "1 W"
Me!SomeTextBox = "1 West"
Case "2 W"
Me!SomeTextBox = "2 West"
Case "GF"
Me!SomeTextBox = "Ground Floor"
End Select

In a query you could use IIf() or the Switch() function.

 

[Brigitte P]

Get an error message 2427 you entered an expression that has no value. But
this isn't true, because if I run the report without the code, the unit
displays just fine but in the short format that I want to change. I thought
I had tried your suggested code before, and I got the same message. What is
it that I'm doing wrong. I actually copied your code into my database.
Brigitte P

I forgot how to do this. I simply want the following. If Unit = "1W" then
say "1 West" or If Unit = "2 W" then "2 West" or If Unit '= "GF" then
"Groundfloor". I know it is simple, but I forgot how to write the
Select Case Unit
Case "1W" ...........and then I'm lost.
It can either occur in the underlying query (and I have no idea how to do
this) or on the report maybe in the On Format property?
Please help.
Brigitte P

Select Case can only be used in code, not a query or control expression. In a
Report's Format property you would use something like...

Select Case Me![Unit]
Case "1 W"
Me!SomeTextBox = "1 West"
Case "2 W"
Me!SomeTextBox = "2 West"
Case "GF"
Me!SomeTextBox = "Ground Floor"
End Select

In a query you could use IIf() or the Switch() function.

 

[Brigitte P]

If I do the following in the query, it does fine:
New Unit: IIf([MultiSelectCriteria]![Unit]="2W","2
West",[MultiSelectCriteria]![Unit])
I only have to put the [MultiSelectCriteria] (which is a query) because Unit
could refer to more than one field. The patients table has unit, and the
doctors table has also unit, which is the relating field.

However, the IIf will accomodate only 1 Unit but I need to do all 5. I was
never able to do multiple IIf functions, thus tried the Case statement.
Don't know what Switch () is.
Please be patient with me; I will need to get this done.
Brigitte P.

I forgot how to do this. I simply want the following. If Unit = "1W" then
say "1 West" or If Unit = "2 W" then "2 West" or If Unit '= "GF" then
"Groundfloor". I know it is simple, but I forgot how to write the
Select Case Unit
Case "1W" ...........and then I'm lost.
It can either occur in the underlying query (and I have no idea how to do
this) or on the report maybe in the On Format property?
Please help.
Brigitte P

Select Case can only be used in code, not a query or control expression. In a
Report's Format property you would use something like...

Select Case Me![Unit]
Case "1 W"
Me!SomeTextBox = "1 West"
Case "2 W"
Me!SomeTextBox = "2 West"
Case "GF"
Me!SomeTextBox = "Ground Floor"
End Select

In a query you could use IIf() or the Switch() function.

 

[Rick Brandt]

Thanks Rick. Yes, I can do it in code. Do I have to declare the new variable
e.g., Dim NewUnit as Integer or something like that? I'll try your solution
right now.
Brigitte P.

What new variable? my code example refers to a hidden control bound to your
field "Unit" and a new unbound TextBox to display the alternate text in
"SomeTextBox". There is no variable.

 

[Rick Brandt]

Get an error message 2427 you entered an expression that has no value. But
this isn't true, because if I run the report without the code, the unit
displays just fine but in the short format that I want to change. I thought
I had tried your suggested code before, and I got the same message. What is
it that I'm doing wrong. I actually copied your code into my database.

Well, if you had copied my code exactly you would need a TextBox on your report
named "SomeControl". Is that what you did?

 

[Rick Brandt]

If I do the following in the query, it does fine:
New Unit: IIf([MultiSelectCriteria]![Unit]="2W","2
West",[MultiSelectCriteria]![Unit])
I only have to put the [MultiSelectCriteria] (which is a query) because Unit
could refer to more than one field. The patients table has unit, and the
doctors table has also unit, which is the relating field.

However, the IIf will accomodate only 1 Unit but I need to do all 5. I was
never able to do multiple IIf functions, thus tried the Case statement.
Don't know what Switch () is.
Please be patient with me; I will need to get this done.

Switch() works like this..

Switch(IfSomeTestIsTrue, "SomeOutput", IfASecondTestIsTrue, "SomeOtherOutput",
etc..)

So for you ...

=Switch([MultiSelectCriteria]![Unit]="1W","1 West",
[MultiSelectCriteria]![Unit]="2W", "2 West", etc..)

 

[Graham Mandeno]

Hi Brigitte

Habe you thought about adding a table - Units - with two fields: UnitAbbrev
and UnitDescr. You can then add this table to your query, joining the Unit
field to UnitAbbrev, and display the UnitDescr field in the query.

An added bonus is that you could then use a combo box with the full
descriptions to populate the Unit field on forms.

 

[Guest]

What a great idea -- there is beauty in simplicity, and I
have many other databases where I have done similar
procedures. Just didn't think of it. The Unit table
already exists as a lookup source, so I don't have to add
much to it.
Thanks.
Brigitte P.

-----Original Message-----
Hi Brigitte

Habe you thought about adding a table - Units - with two fields: UnitAbbrev
and UnitDescr. You can then add this table to your query, joining the Unit
field to UnitAbbrev, and display the UnitDescr field in the query.

An added bonus is that you could then use a combo box with the full
descriptions to populate the Unit field on forms.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I forgot how to do this. I simply want the following. If Unit = "1W" then
say "1 West" or If Unit = "2 W" then "2 West" or If Unit '= "GF" then
"Groundfloor". I know it is simple, but I forgot how to write the
Select Case Unit
Case "1W" ...........and then I'm lost.
It can either occur in the underlying query (and I have no idea how to do
this) or on the report maybe in the On Format property?
Please help.
Brigitte P

.

 

[Brigitte P]

Thanks Rick, both work fine. I really like that I learned about the Switch
because this will overcome the constant frustration with the Immediate If
when I have more than one condition. I'm not a programmer, so I struggle
through code, but my stuff runs amazingly well thanks to the newsgroup. I
selected for this problem the solution by Graham Mandeno, but the next time
I know how to do the Case Select and the Switch (latter probably often).
You wonderful people have taught me a lot, and I'm THE dbase person in my
outfit. How's that, in the land of the blind the one eyed person is king
guess where I fit in.
Brigitte P.

If I do the following in the query, it does fine:
New Unit: IIf([MultiSelectCriteria]![Unit]="2W","2
West",[MultiSelectCriteria]![Unit])
I only have to put the [MultiSelectCriteria] (which is a query) because Unit
could refer to more than one field. The patients table has unit, and the
doctors table has also unit, which is the relating field.

However, the IIf will accomodate only 1 Unit but I need to do all 5. I was
never able to do multiple IIf functions, thus tried the Case statement.
Don't know what Switch () is.
Please be patient with me; I will need to get this done.

Switch() works like this..

Switch(IfSomeTestIsTrue, "SomeOutput", IfASecondTestIsTrue, "SomeOtherOutput",
etc..)

So for you ...

=Switch([MultiSelectCriteria]![Unit]="1W","1 West",
[MultiSelectCriteria]![Unit]="2W", "2 West", etc..)