Update query to Concatonate (1 Viewer)

Locopete99

Registered User.
Local time
Today, 11:33
Joined
Jul 11, 2016
Messages
163
Hi all,

I have an access database that lists numerous customers in a customers table.

This is used in conjunction with a register form to list the customer that a record relates to.

I've been asked if I can change the current drop down to change the customer name to add in the customer postcode where we have multiple customers with the same name but different postcodes (different branches)

I've had to kick every one out already to add in a new field on my back end, which I have created as a text field.

I've tried to run a query to update the table and concatenate the Customer Name and Postcode fields with a space between them. Problem is my code has literally entered "Customer Name" and "Postcode" in the field.

Can someone look at my query code and give me a pointer please. SQL is not a language im great at although I am learning more.

Code:
UPDATE Tbl_Accounts SET Tbl_Accounts.Accpcode = "Account Name" & " " & "Postcode";

I've tried taking the speech marks off but I get an error for missing syntax where I have Account Name and Postcode.

These are my field names though, and not strings that I want to enter.

P.s I know that Customer Name is not a great field name, but I was in a rush when I created it. Not an excuse for sloppy work though I agree!
 

Minty

AWF VIP
Local time
Today, 18:33
Joined
Jul 26, 2013
Messages
10,355
If they are fields then surround them with square brackets.
Code:
UPDATE Tbl_Accounts SET Tbl_Accounts.Accpcode = [Account Name] & " " & [Postcode];
 

Locopete99

Registered User.
Local time
Today, 11:33
Joined
Jul 11, 2016
Messages
163
Thanks Minty!

Worked a treat and I'll keep that in mind for future!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:33
Joined
Oct 29, 2018
Messages
21,358
Hi. The quotes make the values “literals,” so Access will use them as entered instead of evaluating their intended values. However, I would not recommend doing this anyway. You should be able to display both information on your dropdown without updating the table, which would be a bad idea anyway.
 

Locopete99

Registered User.
Local time
Today, 11:33
Joined
Jul 11, 2016
Messages
163
Hi DB,

I did have both values on the drop down list anyway, but the user was using the autocomplete function of the drop down which was leading them to selecting the wrong value as they were taking the "easy" way out. Sadly another example of manipulating a perfectly working database in a bad way to accommodate lazy people!
 

plog

Banishment Pending
Local time
Today, 13:33
Joined
May 11, 2011
Messages
11,611
I've been asked if I can change the current drop down to change the customer name to add in the customer postcode...

You don't do this by reconfiguring your table. There is no need for an UPDATE statement to accomplish this.

Instead, you base the drop down on a SELECT query where you concatenate those pieces of information. Then the drop down shows that value, but uses the primary key field of the table.
 

Locopete99

Registered User.
Local time
Today, 11:33
Joined
Jul 11, 2016
Messages
163
Hadn't thought of that Plog.

Would've been a lot easier to do it that way.

Thanks anyway
 

Minty

AWF VIP
Local time
Today, 18:33
Joined
Jul 26, 2013
Messages
10,355
Actually I didn't read this properly , and I'm with the others - don't store this information simply display it...
If someone changes their address or name you'll have to try and capture that and update this extra field.
 

Locopete99

Registered User.
Local time
Today, 11:33
Joined
Jul 11, 2016
Messages
163
OK, I've gone back and changed the query that runs my drop down to display the info in the right way so I dont have to save it spereately
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:33
Joined
Oct 29, 2018
Messages
21,358
OK, I've gone back and changed the query that runs my drop down to display the info in the right way so I dont have to save it spereately

Glad to hear it. Good luck!
 

Users who are viewing this thread

Top Bottom