Update query not working on where statement?

[paul123456]

UPDATE temp1 INNER JOIN Items ON temp1.MainNum = Items.MainNum SET Items.ItemNum = temp1.ItemNum
WHERE Items.[Date] IS NOT NULL AND cDbl(Items.[Date]) > 0

everything updates ok if i take the where statement out. but for some reason its not working ..i just want to update the itemnum field only if there is no date present in the date field.?? does anyone see anything wrong with this code?



on a side note..Thank you Golom for explaining that Len applies to Text fields ... not dates.

Thanks, PAUL

 

[BNPMike]

You SQL is the opposite of what you've said in English. You're just picking the rows with a non-zero date, instead of the ones without.

 

[TonyJollans]

Hi Paul,

Your problem is probably due to trying to use the CDbl function on Nulls. You have no control over how the database engine chooses to act on your SQL. It may try and do the conversion to double on your dates before ruling them out for being null.

As Date data types are already double, the conversion is unnecessary so you could just remove it and do ..

[blue][tt]... WHERE Items.[Date] IS NOT NULL AND Items.[Date] > 0[/tt][/blue]

Alternatively you could combine the two checks and do ..

[blue][tt]... WHERE CDbl(Nz(Items.[Date])) > 0[/tt][/blue]

.. or, even more simply ..

[blue][tt]... WHERE Nz(Items.[Date]) > 0[/tt][/blue]

BUT, to do what you say rather than what the SQL appears to do, you could probably use ..

[blue][tt]... WHERE IsNull(Items.[Date])[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.