report showing number not name

[rhett7660]

Hi.

I know this has to be easy, but I can't get it to work. The report is generated from a form.... In my report header I have the following code in a text box.

="Test Type:" & [Forms]![frmMain]![testtype]

All that is showing up is the number that is stored in the database.... IE

1=test
2=test2

so it looks like this

Test Type: 1 (TypeID)

When I want it to say

Test Type: Test (Type)

I have tried using the column(1) IE

="Test Type:" & [Forms]![frmMain]![testtype.column(1)]
I have also tried:

="Test Type:" & Forms!frmMain![tblLUtesttype.type]


(tblLUtesttype is the table that I use as a drop down for the different test types...)


but that doesn't work.. how can I get the name not the number..

Thanks
R~

 

[Rich]

="Test Type:" & [Forms]![frmMain]![testtype].[column](1)

 

[rhett7660]

Rich///

thanks for the quick reply.. .still getting the error thou.....

R~

 

[Rich]

What error, bearing in mind that Column 1 is actually the second column ?

 

[rhett7660]

That is correct.. (1) being the second column..

idtype (0)
type (1)


this is the error:

#Name?

R~

 

[Rich]

Then Access doesn't see the column, add a hidden unbound control to your form, set its control source to = [testtype].[column](1), set reference that field on the report

 

[KeithG]

Is this a combo box or list box control that you are referencing on your form? If so that is why it is bring back the key field.

 

[rhett7660]

Rich..

I did what you suggested.... I created a text box with the code you suggested... I get the same error on the form also..

Keith..

Yes it is a drop down.. that is pulling from a table..... the table is tblLUTestType

R~

 

[Rich]

Unhide the textbox and see what's displayed in it

 

[rhett7660]

Rich..

#Name? is showing up in the text box also...

R~

 

[Rich]

Then you have the wrong name somewhere, what is the name of the control?

 

[KeithG]

try this in your control on your report

="Test Type:" & Dlookup("[TestType]","tblLUTestType","[TestTypeID]=" & [Forms]![frmMain]![testtype])


You will need to change [TestType] and [TestTypeId] to fields from your table.

 

[Rich]

Why would anyone use DLookup when the value's already displayed

 

[KeithG]

You do have the form open when you are opening the report, correct?

 

[gemma-the-husky]

try this syntax

="Test Type:" & [Forms]![frmMain]![testtype].column(1)

 

[rhett7660]

Keith..

Yes that is correct.. the form is open when I hit the report open....


I am trying out the different ways and I will post back..

Thanks
R~

 

[rhett7660]

Hello..

Ok I tried both ways...

Keith, when I put your script in I get an #Error? message..

Husky, I get an #name? error....

What Ended up doing was setting up another txt box and having it look at what was in the dropdown box.. like what rich said.. this time I was able to get it to work!

Thanks a bunch for all of your help!

R~

 

[Rich]

There is another way using the Evaluate function, but sometimes the cheats are simpler