Date format autoconverting to time

Access 2003

I have been running into an issue in the past few days that has been
puzzling me, and I have not been able to find a reason for it. If I use VBA
code to set a date value on a table, the value that gets saved to the table
is a time between 12:00:01 AM and 12:00:51 AM. It is always the same time for
a given date, but the time shifts depending on what date I am trying to
assign. I have used the Date() and Now(), DateSerial, and even a combination
of DateDiff and DateAdd, and they are all doing this.

It is doing it on older tables as well as fresh testing tables, and it is
doing it regardless of what formatting I have applied to the field on the
table. If anything it does it more consistently if the format is set to Date
- Short Date. It does it if I type the date into a text box or create the
date from code.

If I am not typing directly on the table then it converts the date to time.

Any ideas of what is going on?

Can Of Worms wrote:

Access 2003

I have been running into an issue in the past few days that has been
puzzling me, and I have not been able to find a reason for it. If I use VBA
code to set a date value on a table, the value that gets saved to the table
is a time between 12:00:01 AM and 12:00:51 AM. It is always the same time for
a given date, but the time shifts depending on what date I am trying to
assign. I have used the Date() and Now(), DateSerial, and even a combination
of DateDiff and DateAdd, and they are all doing this.

It is doing it on older tables as well as fresh testing tables, and it is
doing it regardless of what formatting I have applied to the field on the
table. If anything it does it more consistently if the format is set to Date
- Short Date. It does it if I type the date into a text box or create the
date from code.


It sounds like you date is being converted to a string and
then evaluated as a numeric expression. Best if you posted
a Copy/Paste of the VBA using.

--
Marsh
MVP [MS Access]

It sounds like you date is being converted to a string and
then evaluated as a numeric expression. Best if you posted
a Copy/Paste of the VBA using.

--
Marsh
MVP [MS Access]


In this circumstance, I have a text box ("PAY_DATE") format of Short Date. I
entered the date "6/3/2007"

And my SQL string to append a single record to the table based on the values
entered:
DoCmd.RunSQL ("INSERT INTO tbl_PaymentHistory " _
& "([CaseNumber],[LastName],[FirstName],[PaymentType]" _
& ",[PaymentDate],[PaymentAmt],[Source],[Comment]) " _
& "VALUES (" & CASE_NUMBER & ",'" & LAST_NAME & "','" & FIRST_NAME &
"','" & PAY_TYPE & "'" _
& "," & PAY_DATE & "," & PAY_AMOUNT & ",1,'" & PAY_METHOD &
CheckNumber & "')")

and in the test I just ran at "6/15/2007 8:29:56 AM" (the value of the text
box), the table had no date information, and a time of "12:01:05 AM"

It sounds like you date is being converted to a string and
then evaluated as a numeric expression. Best if you posted
a Copy/Paste of the VBA using.


Can Of Worms wrote:
In this circumstance, I have a text box ("PAY_DATE") format of Short Date. I
entered the date "6/3/2007"

And my SQL string to append a single record to the table based on the values
entered:
DoCmd.RunSQL ("INSERT INTO tbl_PaymentHistory " _
& "([CaseNumber],[LastName],[FirstName],[PaymentType]" _
& ",[PaymentDate],[PaymentAmt],[Source],[Comment]) " _
& "VALUES (" & CASE_NUMBER & ",'" & LAST_NAME & "','" & FIRST_NAME &
"','" & PAY_TYPE & "'" _
& "," & PAY_DATE & "," & PAY_AMOUNT & ",1,'" & PAY_METHOD &
CheckNumber & "')")

and in the test I just ran at "6/15/2007 8:29:56 AM" (the value of the text
box), the table had no date information, and a time of "12:01:05 AM"


Just what I thought. After the contatenation, this part of
your SQL statement:

. . . & "," & PAY_DATE & "," . . .

will look like:

. . . , 6/15/2007, . . .

and because 6 divided by 15 divided by 2007 is a very small
number, it will format as a few minutes after midnight.

The right way to specify a literal date in Access is to
enclose it in # signs:

. . . & ", #" & PAY_DATE & "#," . . .

BUT, that assumes **ALL** users will have their Windows
settings setup to use m/d/y type dates. If there is any
chance that any user at any time in the future might use a
different setting, then you need to specify the date
formatting instead of letting Access use the system setting:

. . . & "," & Format(PAY_DATE,"\#m\/d\/yyyy\#") & "," . . .

--
Marsh
MVP [MS Access]

Perfect. Thank you.

"Marshall Barton" wrote:

It sounds like you date is being converted to a string and
then evaluated as a numeric expression. Best if you posted
a Copy/Paste of the VBA using.


Can Of Worms wrote:
In this circumstance, I have a text box ("PAY_DATE") format of Short Date. I
entered the date "6/3/2007"

And my SQL string to append a single record to the table based on the values
entered:
DoCmd.RunSQL ("INSERT INTO tbl_PaymentHistory " _
& "([CaseNumber],[LastName],[FirstName],[PaymentType]" _
& ",[PaymentDate],[PaymentAmt],[Source],[Comment]) " _
& "VALUES (" & CASE_NUMBER & ",'" & LAST_NAME & "','" & FIRST_NAME &
"','" & PAY_TYPE & "'" _
& "," & PAY_DATE & "," & PAY_AMOUNT & ",1,'" & PAY_METHOD &
CheckNumber & "')")

and in the test I just ran at "6/15/2007 8:29:56 AM" (the value of the text
box), the table had no date information, and a time of "12:01:05 AM"


Just what I thought. After the contatenation, this part of
your SQL statement:

. . . & "," & PAY_DATE & "," . . .

will look like:

. . . , 6/15/2007, . . .

and because 6 divided by 15 divided by 2007 is a very small
number, it will format as a few minutes after midnight.

The right way to specify a literal date in Access is to
enclose it in # signs:

. . . & ", #" & PAY_DATE & "#," . . .

BUT, that assumes **ALL** users will have their Windows
settings setup to use m/d/y type dates. If there is any
chance that any user at any time in the future might use a
different setting, then you need to specify the date
formatting instead of letting Access use the system setting:

. . . & "," & Format(PAY_DATE,"\#m\/d\/yyyy\#") & "," . . .

--
Marsh
MVP [MS Access]