Replace numbers to text

[Kathy]

Hello, I have a textbox on a report that shows number values assigned to
Medication names...such as 1 = Aranesp, 2 = Procrit ...and etc.
On the report, I would like to have the numbers changed back to the text name.
I tried to put an If/Then statement in the On Open Report, but did not work
and received error concerning "cannot put value on this object..."
I am very new at this, so I am not confident in how I am placing this code.
Any help is much appreciated.


Kbelo

 

[krissco]

Hello, I have a textbox on a report that shows number values assigned to
Medication names...such as 1 = Aranesp, 2 = Procrit ...and etc.
On the report, I would like to have the numbers changed back to the text name.
I tried to put an If/Then statement in the On Open Report, but did not work
and received error concerning "cannot put value on this object..."
I am very new at this, so I am not confident in how I am placing this code.
Any help is much appreciated.

Kbelo

Do you have the medication mapping stored in a table somewhere?
Something like this:

tblMedication
(medNumber, medName, blah)

Your report's datasource could be joined with that table (if indeed it
exists) and then the medication names will be accessible to your
report. At this point, change the source of your text box from
medNumber to medName.

-Kris

 

[Kathy]

Thank you for helping and yes, I have another table with just the Medication
Name and MedID. I am pretty much a newbie, so I am not sure how to join the
table to the report...I will do some investigating in that area. However if
you have an article reference or easy example, it is much appreciated.

 

[krissco]

Thank you for helping and yes, I have another table with just the Medication
Name and MedID. I am pretty much a newbie, so I am not sure how to join the
table to the report...I will do some investigating in that area. However if
you have an article reference or easy example, it is much appreciated.

No problem. When designing your report, click "Properties." One of the
properties will be "record source."

The record source will be one of two things: either a saved report/
query (tblMedList) or a SQL statment (select something from something2
where something3 . . .).

You will want to change that record source to include the other table.
If the record source is a SQL statement or a table name, just click
the "..." to the right of the record source, add the medication table
to the query, and join it to whatever was there before by dragging a
line between the two MedicationIDs. You won't want to do this if the
record source is the name of a saved query (as you will change what
already exists). Instead, create a new query from the medication table
and the current record source.

I'm sure this sounds confusing. Just play around with it and I'm sure
you will be able to change the record source.

After you get the med name as one of the fields in your record source,
just add it where you want it on the report.

-Kris