You tried to assign the null value to a variable that is not a variant data type

[chromachem]

We searched online for a solution to this error to no avail.

When we run the "Upsert" code below with tables local to access, tbl537 is updated with values from tblTemp. We do get errors, but changes are reflected.

UPDATE tbl537 
LEFT JOIN tblTemp ON tbl537.LabID = tblTemp.LabID 
SET tbl537.LabID = tblTemp.LabID, 
tbl537.Date = tblTemp.Date, 
tbl537.PFOS = tblTemp.PFOS;

We have switched from storing tables in MY SQL online to MS SQL Server in house because performance online was dismal.

We put tbl537 on the server (dbo_tbl537), as well as tblTemp (dbo_tblTemp)

When we edit the code to point to the dbo files stored on the server, we get the null error.
Other than the primary key, everything is set to allow null values.

We're wondering if the errors we get when we run the code on tables stored locally are related to the null error we get when the tables are stored on MS Server.


We're also wondering if there is an issue due to the fact that tbl537 has more records than tblTemp.

Database is attached.

Thanks for taking a look.

[xps35]

Try

UPDATE tbl537 INNER JOIN tblTemp ON tbl537.LabID = tblTemp.LabID SET tbl537.[Date] = tblTemp.Date, tbl537.PFOS = tblTemp.PFOS;

You can only update related records, so a INNER join will do.
There is no need to update the ID; the ID exists in tbl537 otherwis you can not relate it to the Temp record.

[CJ_London]

For an upsert query this

tbl537
LEFT JOIN tblTemp

is the wrong way round

[zpy2]

execute sql on link table or pass through to sql server?

[chromachem]

A couple of questions:

First off, CJ said I have the statement the wrong way around. We are trying to update tbl537 with values from tblTemp. I'm definitely confused as the above query does just that when executed.
xps35's code works as well.

Secondly, we have moved tables to MS Server. If I execute the following code working with tblTemp (local) and dbo_tbl537 (on the server) we get this error:
"You tried to assign the null value to a variable that is not a variant data type".
dbo_tbl537 was created by exporting local tbl537.

I cannot find any differences in the structue between tbl537 and dbo_tbl537.

UPDATE dbo_tbl537 LEFT JOIN tblTemp ON dbo_tbl537.LabID = tblTemp.[LabID] SET dbo_tbl537.LabID = tblTemp.[LabID], dbo_tbl537.[Date] = tblTemp.Date, dbo_tbl537.PFOS = tblTemp.PFOS;

or

UPDATE dbo_tbl537 INNER JOIN tblTemp ON dbo_tbl537.LabID = tblTemp.LabID SET dbo_tbl537.[Date] = tblTemp.Date, dbo_tbl537.PFOS = tblTemp.PFOS;

Both of the code snippets above give the null variant error when trying to execute the code on a table located on the server.


So...

1) If I do have the code backwards, I am confused as to how to edit the snippet so that it would upsert values from tblTemp to dbo_tbl537.

2) How do I get rid of the null variant error I get when executing code that points to a table that is on our server.

Final note: I get the same errors executing xps35's code on local tables as I do when I execute my original code LOCALLY. When I use either snippet with a table stored on the server - null variant error.

I think it's time to go have a cold craft beer

[CJ_London]

1 just swap the two table names over - left join tblTemp to tbl537
2 the way you have your join now, you will get nulls if there is no match

If you don’t believe me, see this link for how to construct an upsert query in access
https://stackoverflow.com/questions/...ccess#37014004

[chromachem]

Of course I believe CJ.
I did look at the link when I first started. I should have looked longer and saved myself some angst.
It just seems so wrong that when what is wanted is to update the values in tbl537 one references updating tblTemp.
I edited the code (below) to reflect CJ's input, and it works just fine!
Then I went back to the link and reread this comment:
@EmoryLu see PipperChip remark: the code updates a. The trick is that with that construction, if there is no matching row in a, the row will be created. That is the principle of an UPSERT (UPdate or inSERT)

After thinking about it a while, and looking up what left join does, it makes sense.

"The LEFT JOIN command returns all rows from the left table, and matching rows from the left table. The result is null from the right side, if there is no match."

UPDATE tblTemp 
LEFT JOIN dbo_tbl537 ON tblTemp.LabID = dbo_tbl537.[LabID] 
SET 
dbo_tbl537.LabID = tblTemp.[LabID], 
dbo_tbl537.[Date] = tblTemp.Date, 
dbo_tbl537.PFOS = tblTemp.PFOS;

[CJ_London]

It just seems so wrong that when what is wanted is to update the values in tbl537 one references updating tblTemp.

it's just syntax/grammar.

Most developers will default to a left join, but if you want it to 'look right', try using a right join.

Not tested as an upsert but you can try

UPDATE tbl537 
RIGHT JOIN tblTemp ON tbl537.LabID = tblTemp.LabID 
SET tbl537.LabID = tblTemp.LabID, 
tbl537.Date = tblTemp.Date, 
tbl537.PFOS = tblTemp.PFOS;