Expression Builder for Text string manipulation on a lookup table.

[Bosaerohk]

Hi There,



I am new to Access and I am having an issue with text manipulation that directly involves lookup tables.

I am able to get the data in my new table from another table using the lookup command for Field1 from one table and then use I a lookup from another table for Field2 to get the other piece of data.

The new table shows Field1 to be "203-V-2020-CNTR_ON", Field2 to be "_Off_Sw_Stat". When I use an Expression Builder in Field4 that has the following code:

[Field1] & "_" & [Field2]

I get 45_13 for a value as a result of the expression. I need the text concatenated together - i obviously have something fundamentally wrong.

The result I was expecting was the "203-V-2020-CNTR_ON__Off_Sw_Stat". i looked for a concatenate function within access yet I don't readily see one.

Thanks for your help in advance!
Denis

[ItsMe]

...I get 45_13 for a value...

What is a lookup table? are you using LookUp fields while in Design View of your table object? Does the LookUp Field depend on a Value List or a Table?

In your result, 45_13, 45 is the ordinal position of the first lookup and 13 is the ordinal position of the second.

[ssanfu]

Lookup FIELDS are different than lookup Tables. Lookup fields hide what is really happening.
It sounds like "Field4" is grabbing the PK field.

What is the row source for "Field1"?


See
The Ten Commandments of Access
http://access.mvps.org/access/tencommandments.htm
"Rule #2"
then click on the blue "Lookup Fields" at the end of rule#2.

I never us Lookup FIELDs.

[Bosaerohk]

Sorry my terminology is incorrect. I was using a lookup field in datasheet view. As I understand it - the lookup field depends on a table as I am referencing the other table to get the information.
I will look at ordinal of the other table to get the numbers if they correlate.... just need a few minutes as I have a call coming in from a client.

Thank you.