Update Query and null values

[dcmedic]

I have two tables, one keeps the main info the second has maintenance info regarding the main. I created an update query to update table 1 with table 2's revised dates, there are two dates, this works fine until one of two dates does not need to be updated or changed. The query will overwrite a value in table 1 with a null value (empty cell)from table 2. I'm sure there is a simple solution to this.....

Thank you

UPDATE tblLevelA RIGHT JOIN tblLevelAMaintenance ON tblLevelA.ManufacturerSerial = tblLevelAMaintenance.ManufacturerSerial SET tblLevelA.TestDate = tblLevelAMaintenance.PressureVisual, tblLevelA.ValveReplacement = tblLevelAMaintenance.ValveReplacement
WHERE (((tblLevelAMaintenance.DateofTest)=Date()));

 

[traingamer]

Nz(variant, [valueifnull])

Use the nz function to keep the original value if a null is found.

...
SET tblLevelA.TestDate = nz(tblLevelAMaintenance.PressureVisual, tblLevelA.TestDate) , 
...

which essentially checks if PressureVisual is null; if so keep the original value else use PressureVisual value

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[dcmedic]

Thank you very much, I had a few initial problems trying to figure out SQL; I used the "design view" instead. Everything seems to work perfectly now....with the following SQL.

UPDATE tblLevelA RIGHT JOIN tblLevelAMaintenance ON tblLevelA.ManufacturerSerial = tblLevelAMaintenance.ManufacturerSerial SET tblLevelA.TestDate = nz(tblLevelAMaintenance.PressureVisual,tblLevelA.TestDate), tblLevelA.ValveReplacement = nz(tblLevelAMaintenance.ValveReplacement,tblLevelA.ValveReplacement)
WHERE (((tblLevelAMaintenance.DateofTest)=Date()));

Isn't it amazing how such little problems force you to learn something new! Before this I have never heard of NZ(), Thanks again.

 

[Cotnap]

I have moved a database from one Windows Vista PC to another without making any changes. A simple update query - UPDATE [Times & Units] SET [Times & Units].[Date of Return] = Date();-
that adds the current date to records now no longer works. The error message says the 'Function isn't available in query expression Date()'
If the mdb is returned to original machine it starts working again!