Search code examples
sqlvbams-accessstring-lengthmultiple-conditions

MS Access - SQL Inner Join two conditions and Len-Function

I have a database with a table which I use as master and which is being updated and extended on a daily basis by a table with the same layout. Before I update almost the whole master with daily data, I want to test if the values from a specific column changed during the daily update. Usually this column only contains Null or an "X".

As a prototype I only compared the specific column of Table A and Table B and if there is a difference, set a value with more than one characters into the column (here yesterday's date).

This is the code which worked as a prototype:

UPDATE ReiseMaster
INNER JOIN Update_Import
ON(ReiseMaster.Col3 <> Update_Import.Col3 
SET ReiseMaster.Col3  = Date() - 1

Now, the column contains Null, "X" or a date in the master. For the next update I now have to make sure that this previously updated column values which are containing a date as a string will be excluded (otherwise ReiseMaster.Col3 <> Update_Import.Col3 will always be true for them in the future and the date will always be updated which is not intended to happen).

My approach was to exclude all datasets from the master table where the length of the values in the column is longer than 1.

Now here is my problem:

Running the SQL code makes MS Access not responding anymore, the whole program crashes. Can somebody advise me what could be wrong with the following code?

UPDATE ReiseMaster
INNER JOIN ReiseMaster_Import
ON(ReiseMaster.`Attachment Indicator` <> ReiseMaster_Import.`Attachment Indicator` AND LEN(ReiseMaster.`Attachment Indicator`) <= 1)
SET ReiseMaster.`Attachment Indicator`  = Date() - 1

Additional info: I use Access VBA to run a code and during that also the SQL-statements which are being saved in a string. About the reason I add a date once I observe a change, I want to use the dates as a reference when the value has been changed for the first time to do further analysis with them in a later stage.

Solution

  • Avoid using complex joins in update queries! Since the entire recordset needs to be updateable, Access tends to have problems with it.

    Instead, use a WHERE clause:

    UPDATE ReiseMaster
INNER JOIN ReiseMaster_Import
ON(ReiseMaster.[Attachment Indicator] <> ReiseMaster_Import.[Attachment Indicator])
SET ReiseMaster.[Attachment Indicator]  = Date() - 1
WHERE  LEN(ReiseMaster.[Attachment Indicator]) <= 1

    Also, Access uses brackets to escape spaces in column names.

    Note that if you're not using any information from the joined table, and just use it to select records, you should use an Exists clause instead:

    UPDATE ReiseMaster
SET ReiseMaster.[Attachment Indicator]  = Date() - 1
WHERE EXISTS(SELECT 1 FROM ReiseMaster_Import WHERE ReiseMaster.[Attachment Indicator] <> ReiseMaster_Import.[Attachment Indicator])
AND LEN(ReiseMaster.[Attachment Indicator]) <= 1