flexibility to input mm/dd/yy AND mm/yy using input mask in ms 2000

[uscitizen]

why does "99/99/0000;0;_" when used as an input mask in a date/time field not permit for the entry of "partial dates"? i know that without an input mask, entering a month and date, e.g. 6/1998 will produce a (format of medium date) display of 1-Jun-1998, but i would really like to have the extra security of the input mask on a field; i would have thought that what with the number '9' in an input mask field connoting non-required numeric entry, that the above would allow for entering partially known date information in the field. but the error messages i get suggest i'm committing some sort of ms access 2000 'sin'.

 

[WildHare]

the error messages i get suggest i'm committing some sort of ms access 2000 'sin'.

"The fault, dear Brutus, is not in our stars,
But in ourselves, that we are underlings."

If you think about what you're asking Access to do, it may be clearer as to why it is becoming fretful.

To start off, I NEVER use input masks with dates. They just slow things down, and FORCE the user to conform to an unnatural process.

Remember, a DATE assignment will fail if the value entered is not a valid date. So it really SHOULD NOT MATTER how a date is entered. You can DISPLAY it back again using any format you like, but it makes no sense to force a mask on it.

The reason why your mask probably doesn't work is that Access would have no way of knowing how to assign the values in the partial guy - for example, as you noted, "6/98" will default to the date of JUNE 1st, 1998. But so would "06/98" and "6/1998". However, if you attempt to use a leading number that DOES NOT translate to a month, Access gets finicky - e.g., "24/98". Since it's trying to convert the "24" to a month, it has,..well, a bit of a problem.

If you take the input mask off, ALL of these would be perfectly valid date entries:

06/98
6/98
1/6/98
Jun 1 98
1 jun 98
06/01/98
06/1/1998
6/1/1998
june 1 1998
1998 June 1
1998 06 01

However, #19980601# will NOT work, because Access can't really tell that the first four digits are the year - "19" could very well be a DAY in a month. Trouble is, there's no month "98". And I'd bet the date input parsing routine simply isn't resilient enough to go back and forth trying to figure our WHICH four, or possibly, two, digits are the year, and then which digits are the month, and so on.

After all, what day is "10012002" ? October 1, 2002 or February 20th, 1001?

Get my drift? As long as Access can parse your 'chunks' into valid date components, it will know how to do it.

I don't see how it could be much easier. Let your output FORMAT display the date any way you like it.



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[khwaja]

I had a similar issue and instead of being involved in the above discussion on pros and cons, I would share my solution. This will keep you going. Your date will alwayd be saved as first of month.

In your table design view, select the the input mask as mmm-yy (or whatever you want)and save the table. Then assuming that you are using a form for data entry, in the form design view, select the relevant field, change it to combobox, and then cick properties for this field. While you have table/query the as the row source, select build button (...) in row source, and in the QBE, add your original table. Drop the date field down, then use the format function to change dates in the style you want eg Format ([datefield], "mmm-yy&quot. Do not forget to click the group option in the tool bar (sigma/sum sign). When you run the query and if you enteed dates previously, you will see your dates shown as Jan-02, Feb-02. Save the query and the form. In the data entry form, you will see your dates listed using your input mask.

Let me know how you go. Cheers

AK