Date input problem

[Gijs van Swaaij]

In the database on which I am currently working, there is a dynamically
generated form which displays a questionnaire and stores the answers.
It contains one textfield with an inputmask of 00\-00\-0000 to store a
date. The user input of this field is stored (using VBA) in a datefield
in a table. However, the stored value is often different than my input.
For example, if I input 02-05-1988 it stores it as 18-7-1894. What is
the problem? Due to the way the form works, directly linking the
textfield to a record is not an option, it has to be stored using VBA
or the entire database has to be seriously rewritten.

Any help would be greatly appreciated!

Gijs van Swaaij

 

[Douglas J Steele]

Access stores dates as 8 byte floating point numbers, where the integer
portion represents the date as the number of days relative to 30 Dec, 1899,
and the decimal portion represents the time as a fraction of a day.

Access is doing arithmetic on your input: 02-05-1988 equals -1991, and 18
Jul, 1894 is exactly 1991 days before 30 Dec, 1899.

How are you actually storing the data using VBA?

 

[Gijs van Swaaij]

Aaahh. Interesting arithmetics, thanks for the quick explanation! Well,
I guess it wouldn't be extremely user friendly to let the users
calculate the number of days between 30-12-1899 and the date they want
to enter themselves, so I'll want to come up with something else...
Fortunately, I just found the problem
Right now, I have a simple text field in which the users have to enter
the date (with a validation mask to make sure they enter it as
DD-MM-YYYY). As soon as they press the OK Button, a piece of code is
invoked which runs an SQL insert query to insert the date directly into
the database. I only needed to add the '' around the date, and then it
worked. Thanks again for your help!

Gijs van Swaaij