Problem w/ Date inserted/updated to table

[Al]

The code below updates or inserts a "Short Date" formated
field in a table depending on a condition. The problem is
that when I step through the code, the date is correct,
but when my SQL statements update or insert this date into
the table, it posts as "12/30/1899".
The table cell displays the incorrect date in it until you
click on the cell, then all you can see is the
time "12:00:32 AM" ... Help!

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSite, Alarm As String
Dim Msg, Style, Title, Response As String
Dim AC, LID, ALID, LOC As Integer
Dim ALDate As Date

strSite = Me.SITE_NAME
Alarm = Me.[ALARM TYPE]
LID = Me.LOGID
ALDate = Me.DATE

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("AlarmTrack")

ALID = DLookup("[ID]", "ALARM TYPE", "[Type]='" & Alarm
& "'")
LOC = DLookup("[LOC ID]", "LOCATION", "[Site Name]='" &
strSite & "'")

rst.FindFirst "LOGID = " & LID & ""

If rst.NoMatch Then
DoCmd.RunSQL "INSERT INTO AlarmTrack ([LOGID],[LOC ID],
[ID],[ADATE]) VALUES(" & LID & "," & LOC & "," & ALID
& "," & ALDate & ")"
Else
DoCmd.RunSQL ("UPDATE AlarmTrack SET [LOC ID]= " & LOC
& ", [ID]= " & ALID & ", [ADATE] = " & ALDate & " WHERE
[LOGID] = " & LID & "")
End If
rst.Close
dbs.Close

Thank you in advance for your help,
Al

 

[John Vinson]

The code below updates or inserts a "Short Date" formated
field in a table depending on a condition. The problem is
that when I step through the code, the date is correct,
but when my SQL statements update or insert this date into
the table, it posts as "12/30/1899".
The table cell displays the incorrect date in it until you
click on the cell, then all you can see is the
time "12:00:32 AM" ... Help!

It's interpreting the date as a division operation - i.e. 3 divided by
8 divided by 2004 is .000187..., which will indeed be a time like you
describe (Date/Time values are stored as a Double Float count of days
and fractions of a day since midnight, December 30, 1899.

To get a real date use # delimiters around the value being inserted:

DoCmd.RunSQL "INSERT INTO AlarmTrack ([LOGID],[LOC ID],
[ID],[ADATE]) VALUES(" & LID & "," & LOC & "," & ALID
& ", #" & ALDate & "#)"

 

[Jonathan Parminter]

-----Original Message-----
The code below updates or inserts a "Short Date" formated
field in a table depending on a condition. The problem is
that when I step through the code, the date is correct,
but when my SQL statements update or insert this date into
the table, it posts as "12/30/1899".
The table cell displays the incorrect date in it until you
click on the cell, then all you can see is the
time "12:00:32 AM" ... Help!

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSite, Alarm As String
Dim Msg, Style, Title, Response As String
Dim AC, LID, ALID, LOC As Integer
Dim ALDate As Date

strSite = Me.SITE_NAME
Alarm = Me.[ALARM TYPE]
LID = Me.LOGID
ALDate = Me.DATE

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("AlarmTrack")

ALID = DLookup("[ID]", "ALARM TYPE", "[Type]='" & Alarm
& "'")
LOC = DLookup("[LOC ID]", "LOCATION", "[Site Name]='" &
strSite & "'")

rst.FindFirst "LOGID = " & LID & ""

If rst.NoMatch Then
DoCmd.RunSQL "INSERT INTO AlarmTrack ([LOGID],[LOC ID],
[ID],[ADATE]) VALUES(" & LID & "," & LOC & "," & ALID
& "," & ALDate & ")"
Else
DoCmd.RunSQL ("UPDATE AlarmTrack SET [LOC ID]= " & LOC
& ", [ID]= " & ALID & ", [ADATE] = " & ALDate & " WHERE
[LOGID] = " & LID & "")
End If
rst.Close
dbs.Close

Thank you in advance for your help,
Al
.

Hi Al, without seeing the date/time before it's
transposed, I suggest you try using the Format function to
ensure that you store the format as required.

Luck
Jonathan