[RESOLVED] Still problem passing empty date values

[aikidokid]

I originally had the Date fields in my DB set as Text (I know ) but i have now changed then to Date/Time type values.

I am now going through my program and updating the SQL statements to take this into account.

So...

In the following code, I have 1 date value that is Null, but I am getting the error message:
Syntax error in date in query expression '##'

What is wrong with this SQL statement?

            Dim strSQL2 As String
            strSQL2 = "INSERT INTO tbl_EX_Membership"
            strSQL2 = strSQL2 & " Values  (" & rs!Memb_ID.Value & "," & vbCrLf & _
                                         "'" & rs!First_Name.Value & "'," & vbCrLf & _
                                         "'" & rs!Middle_Name.Value & "'," & vbCrLf & _
                                         "'" & rs!Surname.Value & "'," & vbCrLf & _
                                         "'" & rs!Street.Value & "'," & vbCrLf & _
                                         "'" & rs!Town.Value & "'," & vbCrLf & _
                                         "'" & rs!City.Value & "'," & vbCrLf & _
                                         "'" & rs!County.Value & "'," & vbCrLf & _
                                         "'" & rs!Post_Code.Value & "'," & vbCrLf & _
                                         "'" & rs!Home_Tel.Value & "'," & vbCrLf & _
                                         "'" & rs!Mobile_Tel.Value & "'," & vbCrLf & _
                                         "'" & rs!Email.Value & "'," & vbCrLf & _
                                         "#" & rs!DOB.Value & "#," & vbCrLf & _
                                         "#" & rs!Date_Joined.Value & "#," & vbCrLf & _
                                         "#" & rs!Insurance_Due.Value & "#," & vbCrLf & _
                                         "'" & rs!Grade.Value & "'," & vbCrLf & _
                                         "#" & rs!Last_Graded.Value & "#," & vbCrLf & _
                                         "'" & rs!Membership_Number.Value & "'," & vbCrLf & _
                                         "'" & rs!Occupation.Value & "'," & vbCrLf & _
                                         "'" & rs!Notes.Value & "')"
            
           
            cn.Execute strSQL2

debug.Print strsql2
INSERT INTO tbl_EX_Membership Values (47,
'Brian',
'Aurthur',
'Smith',
'22 Anystreet',
'Anytown',
'Anycity',
'Anycounty',
'BG68UY',
'01779999999',
'07968888999',
'',
#22/05/1984#,
#06/07/2007#,
#06/07/2008#,
'White',
##,
'M5678',
'Painter',
'')

[GaryMazzone]

If you want to do that you (use the # sign directly in the statement). You must have a date there.

I still think this is the way to go if not using parameters:

Dim strDOB as String
Dm strDate_Joined As String
Dim strInsurance_Due As String
Dim strLast_Graded As String

strDOB = "NULL"
strDate_Joined = "NULL"
strInsurance_Due = "NULL"
strLast_Graded = "NULL"

If Not IsNull(rs!DOB) Then
   strDOB = "#" & rs!DOB.Value & "#"
End If
If Not IsNull(rs!Date_Joined) Then
   strDate_Joined = "#" & rs!Date_Joined.Value & "#"
End If
If Not IsNull(rs!Insurance_Due) Then
   strInsurance_Due = "#" & rs!Insurance_Due_Graded.Value & "#"
End If
If Not IsNull(rs!Last_Graded) Then
   strLast_Graded = "#" & rs!Last_Graded.Value & "#"
End If

Dim strSQL2 As String
            strSQL2 = "INSERT INTO tbl_EX_Membership"
            strSQL2 = strSQL2 & " Values  (" & rs!Memb_ID.Value & "," & vbCrLf & _
                                         "'" & rs!First_Name.Value & "'," & vbCrLf & _
                                         "'" & rs!Middle_Name.Value & "'," & vbCrLf & _
                                         "'" & rs!Surname.Value & "'," & vbCrLf & _
                                         "'" & rs!Street.Value & "'," & vbCrLf & _
                                         "'" & rs!Town.Value & "'," & vbCrLf & _
                                         "'" & rs!City.Value & "'," & vbCrLf & _
                                         "'" & rs!County.Value & "'," & vbCrLf & _
                                         "'" & rs!Post_Code.Value & "'," & vbCrLf & _
                                         "'" & rs!Home_Tel.Value & "'," & vbCrLf & _
                                         "'" & rs!Mobile_Tel.Value & "'," & vbCrLf & _
                                         "'" & rs!Email.Value & "'," & vbCrLf & _
                                         "" & strDOB & "," & vbCrLf & _
                                         "" & strDate_Joined.Value & "," & vbCrLf & _
                                         "" & strInsurance_Due.Value & "," & vbCrLf & _
                                         "'" & rs!Grade.Value & "'," & vbCrLf & _
                                         "" & strLast_Graded.Value & "," & vbCrLf & _
                                         "'" & rs!Membership_Number.Value & "'," & vbCrLf & _
                                         "'" & rs!Occupation.Value & "'," & vbCrLf & _
                                         "'" & rs!Notes.Value & "')"

[Hack]

I originally had the Date fields in my DB set as Text (I know )

I ALWAYS use text/varchar fields to hold dates. I do my own formatting after I have retrieved them. I'm curious as to why did you changed? Were you have problems that I missed a thread on?

[salvelinus]

I agree with Hack. Dates are a lot easier to work with as text. You can cast them to Date if you need to perform some date calculation.

[aikidokid]

@Gary

I tried what you suggested in this recent thread of mine.
But when I put the values into dtDate, as in that thread, I got the error (from memory) Undefined function dtDate .....something!

@Hack

Yes, can't quite remember when and who, but I was doing some manipulation with dates and was advised by a few to change to Date/Time in the database field.
I did and what I was working on worked ok. Now I have remembered to update the rest of the SQL statements for it to work with the Date type.

It seems to be a lot easier to pass empty strings!

[GaryMazzone]

The dtDate was probably not decalred in the sub. This should work I do this all the time to pass null dates. The other option is to change to parameterized queries. Since I don't do that I'll defer to other to show the proper methods.

[aikidokid]

The dtDate was probably not decalred in the sub.

Hmm, thought it was, but then again...

I will re-write it again and see what happens and get back

Thanks.

[aikidokid]

Gary, thanks again.

I must have done something wrong last time, as this time it's all working.
I have tested it a few times and all seems ok.