Date Conversion

[cruzer330]

I want to take two integer fields (month and year) in a table and make them into a date field int the table. If I can use SQL that would be great, my problem is that I can't assign NULL values to a field

UPDATE tblAll SET tblAll.dob = IIf(IsNull([mm]),"",CDate([mm] & "/1/" & [yy]));

My problem is the Isnull(mm) statement gives me an invalid data type error. How do I output an empty date? iif(isnull(mm),null, ... doesn'e work either.

 

[Rohdem]

Look into using the Nz function

Mike Rohde
rohdem@marshallengines.com

 

[dneufarth]

UPDATE tblTest SET tblTest.[date] = IIf(IsNull([month]),Null,CDate([month] & "/1/" & [year]));

worked for me.

[date] type is 'date/time' and format is 'mm/dd/yyyy'

You might want to make sure neither month or year are null before trying the CDate function.


Dave

 

[cruzer330]

See the problem is that I want to have null values.

The following code gives me an error.

db.Execute ("UPDATE tblAll SET tblAll.dob1 = nz(CDate([dob1m] & " / 1 / " & [dob1y]))&quot

 

[dneufarth]

Not sure if your 'want nulls' comment was directed to me, but if month is null and date had a value, date was changed to null in my test.

Dave