Update a field if date is present??

[paul123456]

I am importing data from excel into a temp1 table..then in a module it runs this query below. what it does is the following

It joins tables temp1 and Items

And takes MainNum on both temp1 table and items table if they are the same

it will then update the Itemnum and overwrite the Itemnum in the Items table with the Temp1 info only if there is a date present in the Items.Date field..for some reason its not working.. the where statement is the problem..because if i take the where statement out it works perfect...but it updates all of the records..i need it to skip the records if it has any date present.. does anyone see were the problem lies? thanks..


UPDATE temp1 INNER JOIN Items ON temp1.MainNum = Items.MainNum SET Items.ItemNum = temp1.ItemNum
WHERE len(Items.Date) > 0;


Thanks, PAUL

 

[Golom]

Len applies to Text fields ... not dates. You probably want
[tt]
WHERE Items.[Date] IS NOT NULL
AND cDbl(Items.[Date]) > 0
[/tt]
To test that the date contains a value and that value is not the default (i.e. 0) which translates to Midnight, December 31 1899.

 

[paul123456]

for some reason its not working..if i take the where statement out it wants to updates the fields..but i can't get it to go..do i have to set up something special in the Items table for the Date field?i have not set up a default value for the field??

Thanks, PAUL