Update query conundrum!
Here is the task:
I need to update field 1d in Table 1 with the two concatenated field values (there are good reasons for doing this). However, the two fields are not necessarily from the same table. If field 2b from Table 2 contains a value, then field 1d is "field 2b & field 1c". If field 2b is empty, then field 1d is "field 1b & field 1c". I am doing it by a VBA statement and function, as below:
strSQL = UPDATE Table 1 LEFT JOIN Table 2 ON Table1.1b = Table2.2a SET Table1.1d = MakeField(Table2.2b, Table1.1c, Table1.1b)
Function MakeField(2b, 1c, 1b) As String
If 2b > "" then
MakeField = "2b & 1c"
MakeField = "1b & 1c"
Table 1 Table 2
The tables are linked by 1b-2a
Here is the problem:
The statement only works where the tables are linked! I thought that the LEFT JOIN made all the records on Table 1 updatable but it performs the task as if it were using an INNER JOIN.
Any ideas would be very appreciated as I have spent hours staring at the problem and performing various experiments!!