RunSQL combine the value of two fields

[grittsm8]

I have two fields (WHNO and case) in a table that I need to combine (concatenate) into another field (join, which I want to be WHNO-case).



What is the best method to accomplish this. I've tried UPDATE, but I don't know how to properly write the SET portion (I don't need the WHERE). I've also tried SELECT...AS... but I haven't had any luck.

I can't find how to properly write the expression to combine the two fields.


Thanks for the help.

[grittsm8]

My current solution is to use an update query to update the join field with the WHNO and case and in the module use the DoCmd.OpenQuery

[June7]

Why do you need to save the concatenated value? Saving calculated data (data dependent on other data) is often bad idea and/or just not necessary. The concatenation can be calculated whenever needed.

[grittsm8]

I need to use the concatenated value as a join with another table. I need the join to make records in the table unique. WHno and case have can be repeated, some WHno records have multiple case records. I need to make sure data from the case field doesn't correspond to each WHno. Concatenating those two values will ensure that the data corresponding to a single WHno and case is correct. I guess I can use a compound key. Or Excel as this data needs to pass through Excel in order to be formatted properly from it's source.

[June7]

Alternative is to build a query that creates field with concatenation expression.

Use that query to join to other table.

Just thought you might want to know options.

[robrich22]

SELECT Whno + "-" + case AS WHNO-Case FROM MyTable;