Select Case in a Report

[kryzystof via AccessMonster.com]

Good Day!

I am working via ODBC with a sql database.
what i want to accomplish is this:

there are drop-downs in the program running from the sql database, where the
user sees opt1, opt2, etc.
and when i look at the tables, i see 0,1,2,4,5,8,etc.
i know which titles belong to which numbers, and in a report i want the user
to see the title, not the assigned value. i can accomplish this witha
textbox and a slew if nested iif's, but it is hard to modify, time consuming,
etc.

i am trying to do a select case statement to take care of this:

sub Report_Open()
Select Case LOCATION (LOCATION is the field that has the assigned
number in it)

case "001"
location_display = "Name1"
case "002"
location_display = "Name2"
etc...

i keep getting an error message that states:
You have entered an expression that has no value

can a select case be done in a report?
is it something in my first line of the select?

TIA

~K

 

[jmonty]

I was thinking about it a bit, and I think that the problem is that there is
no value in LOCATION at the time the report opens. It only gets populated at
the Detail section.
Maybe find another way of retrieving the value for LOCATION such as using the
DLookup function when the value is needed prior to the Detail section.

location_display = Dlookup("LOCATION", "MyTable", "criteria = 'whatever'")

jmonty

 

[scruffy]

Yes you can use a Case statement in reports.
You have to declare your variables and account for Null values.
The error message you are getting can be caused by a Null value or a field
that the report cannot specifically identify.
Use "Me." in front of your field name(s) to let the report coding know
specifically what field you are referring to - Example below.

If IsNull(Me.location_display) then
[some action to take place]
End if

case "001"
Me. location_display = "Name1"
(other case statements for Name#)
End Case

HTH
scruffy

 

[jurczak via AccessMonster.com]

Thank you guys, i was figuring that there was no value at the time it opened,
and i wasn't sure if i could use the 'Me.' format THANKS!

Yes you can use a Case statement in reports.
You have to declare your variables and account for Null values.
The error message you are getting can be caused by a Null value or a field
that the report cannot specifically identify.
Use "Me." in front of your field name(s) to let the report coding know
specifically what field you are referring to - Example below.

If IsNull(Me.location_display) then
[some action to take place]
End if

case "001"
Me. location_display = "Name1"
(other case statements for Name#)
End Case

HTH
scruffy

Good Day!

[quoted text clipped - 30 lines]

~K

 

[kj via AccessMonster.com]

FYI for anyone else who may need it -

Solution:

run the select case in the sub Report_Activate, not Open

use me.whatever for fields - works like a charm!!!

THANKS!

Thank you guys, i was figuring that there was no value at the time it opened,
and i wasn't sure if i could use the 'Me.' format THANKS!

Yes you can use a Case statement in reports.
You have to declare your variables and account for Null values.

[quoted text clipped - 20 lines]