How to handle empty dates in SQL

[Randi W]

Another newbie question:

I have two input fields for dates in my form, I have created them as Text
Boxes with format 'General Date'. The values should be put into date/time
columns in a table in a 'On Click' event. This works fine when there are
some values in the FromDate and ToDate fields:

DoCmd.RunSQL ("Insert into TableA (code1, code2, fromdate, todate) values
('" & Element1 & "','" & Element2 & "', CDATE('" & fromDate & "'), CDATE('"
& toDate & "') ) ")

But - if fromDate or toDate are empty I get this error message:
"Run-time error 3464
Data type mismatch in criteria expression."

I have tried some different ways that include IIf an IsNull, but cannot make
this work..

Thanks for your help!

Randi W

 

[Gerald Stanley]

Try composing the SQL dependant upon whether the text boxes
have values in them e.g.

Dim strSQL as String

If Isdate(fromDate) And IsDate(toDate) Then
strSQL = "INSERT INTO TableA (code1, code2, fromdate,
todate) VALUES etc
ElseIf IsDate(fromDate) Then
strQL = "INSERT INTO TableA (code1, code2, fromdate)
VALUES etc
ElseIf IsDate(toDate) Then
strQL = "INSERT INTO TableA (code1, code2, todate)
VALUES etc
Else
strSQL = strQL = "INSERT INTO TableA (code1, code2)
VALUES etc
End If

DoCmd.RunSQL strSQL

Hope This Helps
Gerald Stanley MCSD

 

[Douglas J. Steele]

Take a look at http://www.mvps.org/access/datetime/date0005.htm at "The
Access Web" for a format you can use, rather than CDate

Global Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l"
Global Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l"


DoCmd.RunSQL ("Insert into TableA (code1, code2, fromdate, todate) values
('" & Element1 & "','" & Element2 & "', " & Format(fromDate, JetDateFmt) &
", "
& Format(toDate, JetDateFmt) & ")"

Use JetDateTimeFmt if you've got dates and times both.