[RESOLVED] Still problem passing empty date values
I originally had the Date fields in my DB set as Text (I know :eek2: ) 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?
Code:
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
Quote:
Originally Posted by Debug.Print
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',
'')
Re: Still problem passing empty date values
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:
Code:
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 & "')"
Re: Still problem passing empty date values
Quote:
Originally Posted by aikidokid
I originally had the Date fields in my DB set as Text (I know :eek2: )
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?
Re: Still problem passing empty date values
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.
Re: Still problem passing empty date values
@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!
Re: Still problem passing empty date values
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.
Re: Still problem passing empty date values
Quote:
Originally Posted by GaryMazzone
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.
Re: Still problem passing empty date values
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. :thumb:
