Fancy Concatenation

[AtlantaBob]

Ok, I know that I can concatenate strings with the "&" operator.

However, concatenating fields that use look-up values give the underlying numeric data, not the string that I want to reference.

e.g., I have a databse that has a field for Street Numbers

1. and Street Names [Streets]. Street Names actually contains a number that looks up the real street name in another table.
   
   When I use
   1. & " " & [Streets], I get something like 213 33213921; or, the real street number, and then the lookup number.
      
      How can I get concatenation to work with lookup data!?
      
      Thanks.

 

[KLin]

use the dlookup function

 

[AtlantaBob]

KLin,

Thanks very much for pointing me to that! I can set it up to retun an extra field in the underlying query, but only if I set it to a constant:

DLookUp("[StreetName]","StreetTable","[StreetID]=10").

I realize that this is a dumb question, but I can't seem to reference the underlying field for the current record. The numeric field is [StrAddressStreet] -- but when I put that in place of the "10" I get the error "The expression that you entered as a query paramater produced this error: "Microsoft Access Can't find the name "StrAddressStreet" that you entered as the expression. I've tried a couple of different references to it--as a table field, query field, etc, but none seem to work. Should I be looking to put something in the criteria box?

Thanks again, I certainly appreciate the help.

 

[KLin]

DLookUp("[StreetName]","StreetTable","[StreetID] = " & [strAddressStreet]) should work.

 

[AtlantaBob]

Wonderful! and Thanks!