Problem with Sub when date is Null

[BobV]

Group:

I have the following sub in one of my modules. When one of the dates that
are passed through to the sub is a Null, the sub enters a date of
"12/30/1899" into the table.

For example, when the PriorSYBegDate variable in my sub is nonexistant
(null), then I want the sub to make no entry into the table for this
variable. If a zero is entered, then I get a date of "12/30/1899" entered
into the table.

How do I get the sub to enter nothing when the date is null?

Thanks,
BobV


'INSERT INTO TABLES
Sub InsertIntoTable(CompanyName As String, BegDate As Date, EndDate As Date,
PriorSYBegDate As Date, PriorSYEndDate As Date)
Dim BackDb As DAO.Database
Dim strSQL As String
Dim PathName As String
On Error Resume Next
PathName = fGetPathName
Set BackDb = OpenDatabase(PathName & "\Asset Database.mdb")
strSQL = "INSERT INTO [MasterData](Name, Beginning, Ending,
PriorSYBeginning, PriorSYEnding, EIN, Sec179Carryover, BusIncome) VALUES
(""" & CompanyName & """, #" & BegDate & "#, #" & EndDate & "#, #" &
nz(PriorSYBegDate) & "#, #" & nz(PriorSYEndDate) & "#, ""95-1234567"", 0,
0);"
BackDb.Execute strSQL, dbFailOnError
Set BackDb = Nothing
End Sub

 

[Tim Ferguson]

VALUES (" & _
""" & CompanyName & """, " & _
BegDate & ", " & _
EndDate & ", " & _
Nz(PriorSyBegDate, "Null") & ", " & _
Nz(PriorSyEndDate, "Null") & ", " & _
"""95-1234567"", " & _
"0, " & _
"0" & _
);"

Actually Wayne, I think that these dates need formatting with ## and proper
mdy or ymd orders etc.

I'd recommend a function like

public function SQLDate(SomeDate as Variant) As String

if isnull(SomeDate) Then
SQLDate = "Null"

elseif isdate(SomeDate) then
SQLDate = Format$(SomeDate,"\#yyyy\-mm\-dd\#")
' or your favourite jet date format...

else ' invalid date: just stick in a Null
SQLDate = "Null"

End If

End function


and then the sql becomes

"INSERT INTO blah blah blah " & _
SQLDate(BegDate) & ", " & _
SQLDate(EndDate) & ", " & _

etc. etc.


Best wishes


Tim F

 

[Wayne Morgan]

Thanks Tim,

You're correct. I modified the strSQL= statement to fix the problem. I double checked it
to make sure it works.

New statement:
strSQL = "INSERT INTO [MasterData](Name, Beginning, Ending, PriorSYBeginning,
PriorSYEnding, EIN, Sec179Carryover, BusIncome) VALUES (""" & CompanyName & """, #" &
BegDate & "#, #" & EndDate & "#, " & IIf(IsNull(PriorSyBegDate), "Null", "#" &
PriorSyBegDate & "#") & ", " & IIf(IsNull(PriorSyEndDate), "Null", "#" & PriorSyEndDate &
"#") & ", ""95-1234567"", 0, 0);"

 

[BobV]

Thanks to both Wayne and Tim for your help. This has helped me immensely.

BobV

Thanks Tim,

You're correct. I modified the strSQL= statement to fix the problem. I double checked it
to make sure it works.

New statement:
strSQL = "INSERT INTO [MasterData](Name, Beginning, Ending, PriorSYBeginning,
PriorSYEnding, EIN, Sec179Carryover, BusIncome) VALUES (""" & CompanyName & """, #" &
BegDate & "#, #" & EndDate & "#, " & IIf(IsNull(PriorSyBegDate), "Null", "#" &
PriorSyBegDate & "#") & ", " & IIf(IsNull(PriorSyEndDate), "Null", "#" & PriorSyEndDate &
"#") & ", ""95-1234567"", 0, 0);"


--
Wayne Morgan
Microsoft Access MVP

Actually Wayne, I think that these dates need formatting with ## and proper
mdy or ymd orders etc.

I'd recommend a function like

public function SQLDate(SomeDate as Variant) As String

if isnull(SomeDate) Then
SQLDate = "Null"

elseif isdate(SomeDate) then
SQLDate = Format$(SomeDate,"\#yyyy\-mm\-dd\#")
' or your favourite jet date format...

else ' invalid date: just stick in a Null
SQLDate = "Null"

End If

End function


and then the sql becomes

"INSERT INTO blah blah blah " & _
SQLDate(BegDate) & ", " & _
SQLDate(EndDate) & ", " & _

etc. etc.


Best wishes


Tim F

 

[Tim Ferguson]

IIf(IsNull(PriorSyBegDate), "Null", "#" & PriorSyBegDate & "#")

Ooh: you murricans! I blame Bill Gates, meself...

Bob has an email address that doesn't tell us where in the world he is, but
more than 70% of the world does not use date formats that will translate
straight from textboxes any/any/any to Jet mm/dd/yyyy... There are some
horrid bugs and dead database waiting on the

VALUES (#01/07/2003#....

error: this will insert a January date, but in all but four countries in
the world the user will have been thinking July. Especially in these
international NGs, it's sensible and defensive to recommend and use the
Format function to avoid misunderstandings.

All the best


Tim (I worry when I argue with MVPs) F