Date input problem

  • Thread starter Thread starter Gijs van Swaaij
  • Start date Start date
G

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
 
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?
 
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 :D
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
 
Back
Top