By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,641 Members | 2,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,641 IT Pros & Developers. It's quick & easy.

SQL - how to handle NULL in a Date field

P: 24
The following code works great if there is a date for interview, but get an error 94, invalid use of null if date field on form is not entered (it is not required and should be null form time to time) how do I alter the code to allow this?

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++
Private Sub Update_Click()
Dim vInterviewee
Dim vInterviewDate As Date
Dim strSQL
vInterviewee = Me.Interviewee
vInterviewDate = Me.InterviewDate
strSQL = "Update tblAAResults
set
Interviewee = '" & vInterviewee & "',
InterviewDate = '" & vInterviewDate & "',
WHERE AAID = " & Me.AAID "
CurrentDb.Execute strSQL, dbFailOnError
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++
Jun 14 '07 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
The following code works great if there is a date for interview, but get an error 94, invalid use of null if date field on form is not entered (it is not required and should be null form time to time) how do I alter the code to allow this?

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++
Private Sub Update_Click()
Dim vInterviewee
Dim vInterviewDate As Date
Dim strSQL
vInterviewee = Me.Interviewee
vInterviewDate = Me.InterviewDate
strSQL = "Update tblAAResults
set
Interviewee = '" & vInterviewee & "',
InterviewDate = '" & vInterviewDate & "',
WHERE AAID = " & Me.AAID "
CurrentDb.Execute strSQL, dbFailOnError
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++
Check whether strSQL evaluates to a valid SQL command when vInterviewDate=Null.
Jun 14 '07 #2

P: 24
Check whether strSQL evaluates to a valid SQL command when vInterviewDate=Null.
I don't follow - the update query works well unless the date field is null then it will crash out on the db.execute line
Jun 14 '07 #3

FishVal
Expert 2.5K+
P: 2,653
I don't follow - the update query works well unless the date field is null then it will crash out on the db.execute line
I mean
"fld=" & Null = "fld" (never "fld=Null")
check whether variable named strSQL evaluates to a valid SQL expression when vInterviewDate=Null.
Jun 14 '07 #4

P: 24
I got the code to work with this modification:

++++++++++++++++++++++++++++++++++++++++++++++++++ +
Private Sub Update_Click()
Dim vInterviewee
Dim vInterviewDate As String
Dim strSQL
vInterviewee = Me.Interviewee


If IsNull(InterviewDate) Then
vInterviewDate = "NULL"
Else
vInterviewDate = "#" & CStr(InterviewDate) & "#"
End If

strSQL = "Update tblAAResults
set
Interviewee = '" & vInterviewee & "',
InterviewDate = '" & vInterviewDate & "',
WHERE AAID = " & Me.AAID "
CurrentDb.Execute strSQL, dbFailOnError
End Sub
+++++++++++++++++++++++++++++++++++++++++++++
Jun 15 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.