Update query to Concatonate

[Locopete99]

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.

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]

If they are fields then surround them with square brackets.

UPDATE Tbl_Accounts SET Tbl_Accounts.Accpcode = [Account Name] & " " & [Postcode];

 

[Locopete99]

Thanks Minty!

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

 

[theDBguy]

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]

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]

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]

Hadn't thought of that Plog.

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

Thanks anyway

 

[Minty]

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]

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]

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!