Formatting dates in text field

[Steve Hayes]

I want to enter dates in a text field in the form yyyy-mm-dd

If I leave it as a plain field, all is well -- I can enter dates in that form.

But it is also possible to make errors in data entry, so I used the following
format and input mask:

0000/-00/-00

to ensure that only numerals are entered and avoid other typos.

The trouble is that the date now displays as yyyymmdd -- without the dashes,
which makes it harder to read.

Is it possible to have an input mask to help data entry, but still have a
correct display?

 

[John Spencer MVP]

IF you want to save the formatted value then change the input mask to
0000/-00/-00;0;_

The zero will save the dashes in all new records entered. You will have to
fix the data already entered.

WHY are you entering dates into a text field? Why not enter them into a
datetime field and if you want to display then in the form yyyy-mm-dd, you can
add a format to the controls to do so.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Beetle]

Change the field back to a Date/Time data type, then in
the Format property of the text box on your form put;

yyyy-mm-dd

Users will be able to enter dates in the normal fashion;

5/21/09

After the date is entered the display will change to;

2009-05-21

 

[Beetle]

Forgot to mention you'll need to get rid of the input mask also.

IMHO input masks only slow down data entry and annoy the users.

 

[Steve Hayes]

IF you want to save the formatted value then change the input mask to
0000/-00/-00;0;_

The zero will save the dashes in all new records entered. You will have to
fix the data already entered.

Thanks very much.

WHY are you entering dates into a text field? Why not enter them into a
datetime field and if you want to display then in the form yyyy-mm-dd, you can
add a format to the controls to do so.

Two reasons:

1) I want to enter dates that are not known exactly -- a year only, or a year
and a month, but not a day. Entering them as 1952-08-00 makes it possible to
sort them.

2) I find date fields in Access incredibly frustrating, because they almost
always reject the data I enter saying that they are "not a valid date format".

 

[Steve Hayes]

IF you want to save the formatted value then change the input mask to
0000/-00/-00;0;_

I tried that, and it put in double dashes when I entered it, then said the
field was too small.

 

[Steve Hayes]

Change the field back to a Date/Time data type, then in
the Format property of the text box on your form put;

yyyy-mm-dd

Users will be able to enter dates in the normal fashion;

5/21/09

That requires far too much calculating in one's head in order to enter it, and
results in a high proportion of wrong entries.

 

[John Spencer MVP]

Two Choices:
1) Increase the field size so it can hold the dashes
Or
2) Use the format property to show the dashes. You will need to use a format
of @@@@-@@-@@

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[BruceM]

What is it that requires calculation? Dates can be entered in any legitmate
format, but will be displayed in the chosen format. I understand you want to
sort first by months that have no day selected (0), then by the first day of
the month, etc, in which case you may do better to enter years, months, and
days in separate number fields. You can combine them as needed using
DateSerial, or sort them by year, month, and day field (use other field
names in this case, as Year, Month, and Day are reserved words in Access).

 

[Steve Hayes]

Two Choices:
1) Increase the field size so it can hold the dashes

The field size is 10 characters, which should show the dashes, but not the
extra ones that particular format put in: yyyy--mm--dd

Or
2) Use the format property to show the dashes. You will need to use a format
of @@@@-@@-@@

OK, I used 0000-00-00