Solved Concatenation producing weird result (1 Viewer)

Abes

New member
Local time
Today, 23:14
Joined
Jul 2, 2021
Messages
21
Need some know how here please. I have created a query from a single table and have several columns which amongst others includes one for "surname" and one for "initials". When I run the query the surnames and initials show up, no problem there.
When I however try to concatenate the two fields in a new query column using the following syntax

Expr1: [surname] & [initials]

and then run the query the result for this concatenated column is a list of two digit numeric values instead of the combined surname and initials. Nowhere else in the table is there fields that contain these two digit numeric values so where they are coming from is a mystery to me.

What am I doing wrong?
 

Minty

AWF VIP
Local time
Today, 21:14
Joined
Jul 26, 2013
Messages
10,355
Have you used lookup fields in your tables?
 

Abes

New member
Local time
Today, 23:14
Joined
Jul 2, 2021
Messages
21
Yes
 

Abes

New member
Local time
Today, 23:14
Joined
Jul 2, 2021
Messages
21
Thanks for the bad news - lol. That is clearly where my problems are coming from, some serious work waiting for me to fix that lot up................
 

Minty

AWF VIP
Local time
Today, 21:14
Joined
Jul 26, 2013
Messages
10,355
It's not impossible to work around, but the best way is to make the look values and table relationships yourself and remove the lookups.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:14
Joined
Sep 21, 2011
Messages
14,047
For the time being, bring in the descriptive data from the lookup tables into the query, and concatenate that.?
 

Abes

New member
Local time
Today, 23:14
Joined
Jul 2, 2021
Messages
21
Will look at whats going to to work for me, thanks.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:14
Joined
May 7, 2009
Messages
19,169
ome serious work waiting for me to fix that lot up
if they are coming a numbers then check the lookup table name and add it in the
query.

see query1 on how to do it.
 

Attachments

  • lookuptables.accdb
    468 KB · Views: 458

Abes

New member
Local time
Today, 23:14
Joined
Jul 2, 2021
Messages
21
Thanks all, sorted, without your assistance I would probably still be clicking around aimlessly.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:14
Joined
Oct 29, 2018
Messages
21,358
Thanks all, sorted, without your assistance I would probably still be clicking around aimlessly.
Hi. Congratulations! Would you mind sharing your solution in case others could also benefit from it? Thanks.
 

Abes

New member
Local time
Today, 23:14
Joined
Jul 2, 2021
Messages
21
Followed the advice given by Minty and "make the look values and table relationships yourself and remove the lookups", this after having read the article you pointed me to regarding the evils of lookups in tables.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:14
Joined
Oct 29, 2018
Messages
21,358
Followed the advice given by Minty and "make the look values and table relationships yourself and remove the lookups", this after having read the article you pointed me to regarding the evils of lookups in tables.
I'm sure you made the right decision. Good luck with your project.
 

Users who are viewing this thread

Top Bottom