Hi,
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"
Else
MakeField = "1b & 1c"
End If

Table Diagram:
Table 1 Table 2
1a 2a
1b 2b
1c 2c
1d

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!!

Hey,

   Your function is going to need to be a little more phistimicated. :D

   It sounds like you are doing all of this in a query.

   I suppose you could put this function on a button's on click event. Then open the first table as a recordset, run through each record and compare it to the second table (as a recordset) and then do your checking for null in the second table, and concatenate based on the results. Then perhaps put the results in a hidden table, run the update query based on the hidden table, and then clear the hidden table.

   You know, there is more than one way to skin a cat in VBA, and I am way sure that another user could come up with a more elegant solution. But if you want to pursue this one, lemme know.

HTH

mmcdonal

Thanks,
Geeeeez! Sounds a bit convoluted but may execute quickly enough. Can you give me some "skeleton code?
I appreciate it.
Tim

I worked it out.

I worked it out.
Thanks