Why does this insert 30/12/1899 in the field and 00:00:43 in the table?

  • Thread starter Thread starter Vivista Eastbourne
  • Start date Start date
V

Vivista Eastbourne

Here is my code

Dim STRSQLmailshotdate As String
Dim insertDate
insertDate = Format(Now, "dd/mm/yyyy")
STRSQLmailshotdate = "UPDATE tblMailingList SET DateMailshot = " &
insertDate & " WHERE ((SelectforPrint = -1));"
DoCmd.RunSQL STRSQLmailshotdate
chkMailshotDone.Value = True


Many Thanks

A
 
Hi, Vivista.

The following is formatting the current date and time into a string:

insertDate = Format(Now, "dd/mm/yyyy")

Since it's not in Date/Time format, this string won't be recognized as the
current date when the records are updated, because Jet will try to convert
the string into a Date/Time data type. Instead, try:

Dim STRSQLmailshotdate As String
Dim insertDate As String

insertDate = Format(Now, "dd/mm/yyyy")

STRSQLmailshotdate = "UPDATE tblMailingList SET DateMailshot = #" & _
insertDate & "# WHERE ((SelectforPrint = -1));"
DoCmd.RunSQL STRSQLmailshotdate
chkMailshotDone.Value = True


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
5/5/2006 = 4.985045E-04
That is, 5/5 = 1, 1/2006 = 4.985045E-04

0.0004985045 is 43 seconds past midnight on the 30th December, 1899.

For comparison, May 5 2006 is approximately
(2006-1900)*365.25 + (30.5) *5 + 5 = 38843.5

actually, 38842, not 38843, because we should have started at 30/12/1899
instead of just using '1900'.

(david).
 
Back
Top