Problem w/ Date inserted/updated to table

  • Thread starter Thread starter Al
  • Start date Start date
A

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
 
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!
Click to expand...

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 & "#)"
 
-----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
.
Click to expand...
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
 
Back
Top