INSERT INTO statement from recordset needs to support null date values

[frogmann]

Hello,
I'm continuing to have a bear of a time trying to accommodate null values in my insert statement. In particular (a simplified version):
sql1 = "INSERT INTO tblStudent ( [DOB] ) VALUES ( #" & rs1![DOB] & "#) ;"

This statement works fine when there is a date in DOB. But when it is null, I get a run-time error 3075: Syntax error in date in query expression ##.

Ok, so I tried an IIf statement based on another forum I saw, as follows:
sql1 = "INSERT INTO tblStudent ( [DOB] ) VALUES ( " & IIf(IsNull(" & rs1![DOB] & "), Null, "#" & rs1![DOB] & "#") & ") ;"

The above gives me the same syntax error. I've also tried it with quotes around "Null", still no joy. I simply want to accommodate possible null values in my date fields. The actual query is much longer, has several of these date fields (including others) and gets quite messy with these IIf statements. Any suggestions would be very much appreciated. Thanks!

 

[PWise]

try

Nz("#" + dr + "#", "null")
sql1 = "INSERT INTO tblStudent ([DOB]) VALUES (" & Nz("#" + rs1![DOB] + "#", "null") & ") ;"

 

[PWise]

sorry Sb

sql1 = "INSERT INTO tblStudent ([DOB]) VALUES (" & Nz("#" + rs1![DOB] + "#", "null") & ") ;"

note: the plus "+" signs not the Ampsand "&"

 

[frogmann]

Hi Pwise, thanks much for your quick response! Sorry, it looks like your last 2 code samples were identical. I tried it and now get a run-time error '13', type mismatch. Is there something you were thinking to do differently on your second response? I'm wondering if Nz() is just for numbers, not dates, thus the type mismatch error?
Again, any and all responses are very much appreciated.

 

[PWise]

what i wanted with the 2nd post is to take out the first line that i copied from my code

try

sql1 = "INSERT INTO tblStudent ([DOB]) VALUES (" & Nz("#" + cstr(rs1![DOB]) + "#", "null") & ") ;"

 

[frogmann]

Thanks for the update pwise. Unfortunately I now get a run-time error 94: Invalid use of null. I've looked all over the web for an answer to this, and I have yet to find a solid, definitive answer that works. Whenever this gets solved I'm going to post it everywhere I can. So many problems for what should be a simple sql query.
Thanks again for any other suggestions anyone may have!

 

[PWise]

what do you get when you do

sql1 = "INSERT INTO tblStudent ([DOB]) VALUES (" & Nz("#" + cstr(rs1![DOB]) + "#", "null") & ") ;"
debug.print sql1

 

[JoeAtWork]

The trouble spot is in red:

sql1 = "INSERT INTO tblStudent ([DOB]) VALUES (" & Nz("#" + cstr(rs1![DOB]) + "#", "null") & ") ;"

If DOB is Null you are going to get an error because you are putting Null in the CStr function.

I would do something like this:

Dim strDate As String
Dim sql1 As String

If IsNull(rs1![DOB]) Then
  strDate = "Null"
Else
  strDate = "#" & Format(rs1![DOB],"mm/dd/yyyy") & "#"
End If

sql1 = "INSERT INTO tblStudent ([DOB]) VALUES (" & strDate & ")"

Sometimes it's easier to keep track of your logic with more lines of code than trying to cram everything into one line.

By the way, Nz is not just for numbers, it's first argument takes a Variant data type, so that can include numbers, strings and dates. I would advise always using an ampersand for string concatenation.