Date Format Problem - mm.yy

  • Thread starter Thread starter Gina
  • Start date Start date
G

Gina

Hi.

I am storing MOT as a date field in a table. .... unfortunately when user
types 04.05 ( meaning april 2005 - in Germany ) access creates stuff like
04.05.2005 .....- 4th of may :(

I am struggeling around on this date stuff already the whole morning ....
and I can't see any land on this ...

I changed the format of the field in the table to be mm.yy - without an
input mask .....
same in the form field ....

when I click on the form field it shows the full date .... 04.05.2005 ...

how can I avoid all this .... what I want is the user to be able to just
type in month.year ... access to make the year out of the last 2 digits,
store it exactly like this in the table, and the form should show it in the
same format even I click on it!!

please ... any help on this most appreciated

Gina
 
A date/time field always contains a complete date, Gina, there's no way that
you can store a partial date in a date/time field.

The simplest solution is probably not to use a date/time field, but to use
two integer fields, one for the month and one for the year.

The alternative would be to use unbound text boxes, and write code in the
After Update event procedure of the text boxes, and the Current event
procedure of the form, to translate between the contents of the two text
boxes and the complete date/time value stored in the date/time field. The
code would assign a consistent value to the day part of the date, e.g.
always the first of the month, or always the last of the month. Whether it
is worth taking this more complicated approach depends on what kind of
date-based calculations you may need to perform on the data.
 
Brendan,

thanks for your explanation re the date/time stuff ..... but it doesn't
really make me happy !!!!
something strange anyway .... it works fine as long as the year is before
2000 !!!! so it shows and stores everything fine when it is 12.98 - dec 98

this access .... blyme ..... well ... I have to think about what I am going
to do !!!
would it be another option to make the field text in the table and do
whatever conversions needed (for the code already written !!!! outch) on vba
level ???

whatever option I think there is none will be without pain!!! at that level
.....actually thought that's an easy thing to care about later :((

Gina
 
You could use a single text field, but if you need to convert it to a date,
it's actually easier to do that with two integer fields. For example, the
DateSerial function returns a date, given three integers (for the year,
month, and day). So if you use a text field, you first have to parse it
(separate the year and month) then convert from string to integer before you
can call the DateSerial function. Using integer fields avoids all that.

Using integer fields also means you don't have to worry about how the user
enters the data. If you use a text field, one user may enter 12.98, another
user may enter 12/98, someone else may enter 12-98, etc. Integer fields will
accept only digits.
 
Thanks, Brendan ....

Think your suggestion with int field is the easier way being in this kind of
dilemma.

thanks for your help!!!!

Gina
 
Gina said:
I am storing MOT as a date field in a table. .... unfortunately when user
types 04.05 ( meaning april 2005 - in Germany ) access creates stuff like
04.05.2005 .....- 4th of may :(

I am struggeling around on this date stuff already the whole morning ....
and I can't see any land on this ...

I changed the format of the field in the table to be mm.yy - without an
input mask .....
same in the form field ....

when I click on the form field it shows the full date .... 04.05.2005 ...

how can I avoid all this .... what I want is the user to be able to just
type in month.year ... access to make the year out of the last 2 digits,
store it exactly like this in the table, and the form should show it in the
same format even I click on it!!
Click to expand...


I'm with Brenden on how to avoid this issue, but it might
help if you understood how Access converts what you type to
a date value. It is pretty confusing because Access tries
too hard to make sense of a bunch of characters.

If the two values can be interpreted as a month and day
(e.g. 4-9), then Access will automatically supply the
current year (9 April 2005). If the month or day won't work
(e.g. 4-99), then Access uses the too large number as the
year and automatically supplies the first day of the month
(1 April 1999).

There are a bunch of other issues involving the delimiter
character, especially with different regional settings in
Windows.

Bottom line is use two text boxes or teach your users to
enter a four digit year.
 
Back
Top