Appending Date Data from Access to SQL Server but some fields are NULL and Causing Error Box

Question

I have created an append query in Access (Office 365) to append among other things date data to a table in a SQL Server but it gives me an error (Type Conversion Failure) when "sending" NULL's.

I have tried the following, all of which send the dates data to SQLServ but doesn't like the NULL:

Status_Date_New: IIf(IsNull([StatusDate])=True,"Null",[StatusDate])

Status_Date_New: IIf(IsNull([StatusDate])=True,"",[StatusDate])

Status_Date_New: Format(NZ([StatusDate], '1900-01-01 00:00:00:000', [StatusDate])

Status_Date_New: NZ(Format([StatusDate], "yyyy/mm/dd hh:nn:ss"), '1900-01-01 00:00:00:000')

Status_Date_New: NZ([StatusDate],"NULL",[StatusDate])

Just variations on a theme I could go on with the things I have tried but I didn't write all of them down, thought maybe one of you smart people had something that works.

Answer 1

SQLServer = datetime

Access = Date/Time

Answer 2

The source data might contain a zero length string or a space in the StatusDate field. I would spend some time exploring the source data and maybe try a subset of the records.

Answer 3

Then it's obviously a problem with data types.

What is the data type of the field in the source table?

What is the data type of the field in the destination table?

Answer 4

Just sending the field creates the error as well, that's what got me down the rabbit hole. I'm just showing you everything I have tried up to this point, I'm not saying those should have worked.

Answer 5

If your are appending to a date type field, then you don't need an IIf statement, just append your StatusDate field.

Your IIf statement is trying to append the string value of 'NULL', That is not the same as an actual NULL.