How do I enter a null date in a SQL Insert statement?

[David_Ssc]

In a macro I am trying to copy data from a file and Insert a new record into a table. Some of the date values in the file are missing. When I try to in Insert a null date it gives me a SQL exception. What do I need to do to a NULL date to create a null value in the table?

  ' Values read from file
	instr1 = "hello"
	instr2 = "world"
	indate1 = ""
' Macro processing
	indatec = Format(indate1,"yyyy-mm-dd")
	sSQL = "INSERT INTO ""aTable"" (""str1"",""date1"",""str2"") VALUES ('" _
		& instr1 & "','" & indatec & "','" & instr2 & "');"
	ostatement.executeQuery(sSQL)

Thanks, David

[librebel]

hello @David_Ssc,
try enter “NULL” for the value, HTH

[David_Ssc]

Didn’t know what you meant at first, but finally arrived at:

indatec = "NULL"
if indate1 <>"" then indatec = "'" & Format(indate1,"yyyy-mm-dd") & "'"
sSQL = "INSERT INTO ""tmpTable"" (""str1"",""date1"",""str2"") VALUES ('"
sSQL = sSQL & instr1 & "'," & indatec & ",'" & instr2 & "');"

Adding the quote marks for valid dates prior to building the SQL statement. Thanks.