Join a concatenated field

[Aaron]

Hi,

I need your help to figure out how to join a concatenated
field. In a query, I have joined the first name, a space
and the last name into a field (First_Name & ' ' &
Last_Name). Is there a way to convert this concatenated
field into a field that can be joined with another field
in another table? I tried to create a table and pasted
the results into the table but somehow it still seems to
remember that it is a concatenated field. I keep getting
the following message: Cannot join on Memo, OLE, or
Hyperlink Object. Does anyone know how to get around this?

Thank you,
Aaron

 

[Andrew Smith]

You shouldn't need to do this if your database is properly designed, for the
simple reason that the name should only be stored in a single location. I'm
not sure whether it can be done or not (though I suspect it can't), but even
if it could be done it would not be a good idea. You could easily have two
identical names referring to two different people and your system would have
no means of distinguishing them.

 

[Guest]

Unfortunately, the database was not that well designed - vendor designed this. I forgot to mention that I also changed the concatenated name to a primary key as there should only be one instance of the name in this table. Although, it could show up multiple times in the table that I am trying to join to

The message seems to indicate that the concatenated field is a Memo, OLE, or Hyperlink Object. When I created the table to paste the query into, it still seems to think that it is a field other than the text that I tried to setup for it

Any other ideas?
----- Andrew Smith wrote: ----

You shouldn't need to do this if your database is properly designed, for th
simple reason that the name should only be stored in a single location. I'
not sure whether it can be done or not (though I suspect it can't), but eve
if it could be done it would not be a good idea. You could easily have tw
identical names referring to two different people and your system would hav
no means of distinguishing them

 

[John Vinson]

Cannot join on Memo, OLE, or
Hyperlink Object.

It sounds like you're trying to Join your calculated concateneation to
a Memo field - i.e. it's the OTHER table field that's causing the
error, not the concatenation! Is that the case?

 

[Aaron]

Hi John,

Thank you for that info.....I can't believe I didn't look
there first! I just figured that the fields that I
extracted from the application were all in text. I didn't
think that this would be the case. Oh brother! Thank you
for your help!

Aaron

 

[John Spencer (MVP)]

John V,

I bow to the guru. I had no idea on what might be causing the problem. Good job.

John S