Date != Date()?

  • Thread starter Thread starter Adrian Parker
  • Start date Start date
A

Adrian Parker

in VB I change the value of a Date Time Picker (MM/DD/YYYY), and use the
following code:

db.Execute ("INSERT INTO payments (studentCode, datePaid, amountPaid,
cardCheckNum) " _
& "VALUES ('" & studentCode & "', " & dtpDate & ", '" &
Val(txtAmount) & _
"', '" & txtCardCheckNum & "')")

The year in Access is showing as 1899.

Access is using Short Date notation for the field in question. So they
should match.

Why is an input date of 2004 translated to 1899 by Access?


Adrian
 
Adrian Parker said:
in VB I change the value of a Date Time Picker (MM/DD/YYYY), and use the
following code:

db.Execute ("INSERT INTO payments (studentCode, datePaid, amountPaid,
cardCheckNum) " _
& "VALUES ('" & studentCode & "', " & dtpDate & ", '" &
Val(txtAmount) & _
"', '" & txtCardCheckNum & "')")

The year in Access is showing as 1899.

Access is using Short Date notation for the field in question. So they
should match.

Why is an input date of 2004 translated to 1899 by Access?
Click to expand...

Ok, I guess this way is correct:

db.Execute ("INSERT INTO payments (studentCode, datePaid, amountPaid,
cardCheckNum) " _
& "VALUES ('" & studentCode & "', #" & dtpDate & "#, '" &
Val(txtAmount) & _
"', '" & txtCardCheckNum & "')")

Why do I need to wrap the date in the # symbol?


Adrian
 
It may (or may not) be due to your SQL statement.
It may see the DateTime field as a string or number
and is doing a Julian conversion.
When using date fields in SQl Statements, enclose your
date literals with the number sign (#).
i.e. #02/27/2004#
This way Access knows that the value is a Date field.
See Access Help:
date literals, specifying in SQL statements
(you may have to surround the "#" with quotes
and concatenate them in your case)

Hope this helps.

jmonty
 
jmonty said:
It may (or may not) be due to your SQL statement.
It may see the DateTime field as a string or number
and is doing a Julian conversion.
When using date fields in SQl Statements, enclose your
date literals with the number sign (#).
i.e. #02/27/2004#
This way Access knows that the value is a Date field.
See Access Help:
date literals, specifying in SQL statements
(you may have to surround the "#" with quotes
and concatenate them in your case)
Click to expand...

Thank you.


Adrian
 
Ok, I guess this way is correct:

db.Execute ("INSERT INTO payments (studentCode, datePaid, amountPaid,
cardCheckNum) " _
& "VALUES ('" & studentCode & "', #" & dtpDate & "#, '" &
Val(txtAmount) & _
"', '" & txtCardCheckNum & "')")

Why do I need to wrap the date in the # symbol?
Click to expand...

So that Access will recognize it as a Date rather than a mathematical
expression - 2/27/2004 can mean 2 divided by 27 and then that divided
by 2004. This gives about 0.0000369 which corresponds to a Date/Time
value of #12/30/1899 00:00:03# - since zero is equivalent to midnight,
December 30, 1899.
 
John Vinson said:
So that Access will recognize it as a Date rather than a mathematical
expression - 2/27/2004 can mean 2 divided by 27 and then that divided
by 2004. This gives about 0.0000369 which corresponds to a Date/Time
value of #12/30/1899 00:00:03# - since zero is equivalent to midnight,
December 30, 1899.
Click to expand...

Ahhhhhh

That makes perfect sense.

Thank you.



Adrian
 
Back
Top