Full Version: Date Input Mask for two different formats
UtterAccess Forums > Microsoft® Access > Access Forms
Hi Everyone!
I'm trying to have a Date Input Mask this is what Im using "99/99/9999;;_"
It works fine for a complete date e.g. 01/09/1997
But there are some date entries where its only the year e.g. 1997 and when I try to enter this, Access returns an error.
Is there a way for the Date Field to accept both formats, "01/09/1997" and "1997" ?
R. Hicks
You must use the "/" between the m/d/yy ...
o you enter 1 then / then 9 then / then 97 ....
Sorry cant seem to figure it out. :-)
There are the masks that I've used
when I enter 01091997 it results to 01/09/1997 and thats ok.
now when I enter 1997 it throws an error and wont accept it because of the input mask.
Im entering companies' dates of establishment. there were data where its complete and there were just the year.
Thanks Ricky
R. Hicks
You will not be able to just enter 1997 when using an Input Mask ...
HAs I replied earlier .. you must enter the "/" to seperate the month, day, and year value when entering a partial date value ...

art of the probem here is that you are not distinguishing between a Date, which has a specific definition, and a Calendar year, which also as a specific, BUT DIFFERENT, meaning.
All dates consist of three parts: Month (January through December), Day (1 through 28,28, 30 or 31) and Year.
If you are storing a date in a field, it must have all three parts, not just one. 2005, for example, is not a date, it is a calendar year.
Access CAN'T accept a partial date in a date field. You can't store a month as a date, you can't store a day as a date and you can't store a year as a date. It just won't work. You have to have all three compenents to specify a date.
Odon't know that it matters whether there is an input mask on the control where you are trying to enter the value, but I could be wrong on that point. It's the fact that the underlying field is a date that is the problem.
Now, if you define the field in the table as a text data type instead date, you might be able to make it accept either dates or calendars, but they won't be recognized by Access as date values.
R. Hicks
They are trying to enter 1997 ... meaning: 1/9/97
This format can be entered when using an input mask .. but the forward slash will have to be use to indicate and seperate the month, day, and year values to Access ...
I think he means either 1/9/1997 as the date, or, if he only HAS the year, "1997"...
R. Hicks
In the original post .. is says:
Meaning 1997 to be interpreted as 1/9/97 (using an input mask) ...
That answers it!
Thanks alot Ricky, George!
That's what I keyed on.
R. Hicks
Good catch ... I did not see that.
hi George , Ricky
knew I had to explain what I did, sorry I replied just now.
The data I have is either a complete date (mm/dd/yyyy) or just the year (yyyy).
So I followed George's advice. I changed the Data Type to Text and then play around with the input mask.
What I have as Input Mask for the field "Date Established" is
!>a<aa" "99", "9999;0;_
Now it accepts both entries:
1) "Aug171977", which results to "Aug 17, 1977"
2) "1977", which results to "1977"
The problem here though is even when I enter say:
XYZ (not the first three letters of a month)
Aug17 (without the year)
171977 (without the month)
For any other combination, it's still acceptable or passes the input mask. I dont have any control over it.
I want to limit the user's input to acceptable levels so as to eliminate error. Like for characters, just the first three letters of the month, and if the month is entered, it would require the day and the year, and so on.
For now I'll just have the proper format in the "Description" so the user will be guided accordingly.
But nevertheless, it's really a big help to have you guys assist me.
Thanks alot!
By the way I'm doing this favor for a friend to manage his records. But the one who'll enter the data is not very computer literate, so I have to limit possible entries to the actual entry required to avoid errors as much as possible.
I feel like I may have misled you somewhat here. As I said in that post, if you convert the field from date datatype to text datatype, Access no longer recognizes the field as a date. That allows you to enter both dates and calendar years. The problem is that that may not be such a good thing, as you're seeing. Access will also accept ANYTHING ELSE you enter, whether it is a valid date or not. Sorry I didn't think through all of the implications of that suggestion.
lso, if you permit the user to enter both dates (9/1/1997) and calendar years (1998) into this one field, you lose the ability to filter or sort on that field in subsequent queries. If that is going to be important, and it probably is, then it seems like you need to think further about how you really want to capture this data. In other words, if you want to be able to sort or filter a report on the year in which a company was established, you'll have a really hard time doing so. And if you want to filter or sort on the basis of month or date, it will be impossible.
So, the business rules that apply to the application are very important. As long as the organization only needs to have this information available on a case by case basis, a "mixed" field might not be so bad, but if there is any possibility that this data will be used for grouping, sorting or filtering, then it definitely is not appropriate.
It might be preferable in your case to break your establishment date out into three separate fields: Month, Day and Year. That way you can capture information consistently. The month and day fields can be optional, while the Year field, with an input mask like "9999", is required.
Again, my apologies for not thinking this through more thoroughly in my first response.
Hi Again
ont worry about it George, that's ok. After all, I need more practice. So each correspondence encourages me to learn more.
Here's what I did, trying to follow your advice and my need to limit entry to eliminate errors.
Omade three look up tables for month (January-December), day (1-31) and year(1900-2006) respectively. All three related to a separate DateEstablished Table.
The month and day table's first field has an entry of "Unknown" except the year which is required.
This way, the user just have to select the entry and dont have to type it in, does eliminating errors. Also, I can filter and/or sort based on the year.
Is this a good approach?

Lookup tables are often very useful. Normally, separate date part fields would be a bit of overkill, but in this situation having default values of "Unknown" for Month and Date allows you to handle the specific nature of the data you are working with.

Having a separate table for DateEstablished, though, may not be necessary. If a company is established once, it is established. It won't, or shouldn't, have a second date of establishment. It can undergo one or more name changes, it can re-locate to one or more new places, but it can only come into existence once.

In thinking about how to set up tables, I like to distinguish between attributes which are STATICand those which are SEQUENTIAL. What I mean by static attrribites are those which can have only one value for a given entity. Sequential attributes can have different values at different times for any given entity. Date of establishment seems to me to be of the static sort, it happens once and shouldn't change. An example of a sequential attribute might be, for example, an address. An organization may re-locate from time to time. Each time it does so, the address attribute for that organization changes.

Here's why distinguishing between static and sequential attributes matters in thinking about table design.

By its nature, a static attribute doesn't change, so it can be stored in the same table as the entity itself without risking loss of information. Once you record it, you never have to be considered that it will change, with one exception. That would be correcting an error in the original entry.

However, because sequential attributes do change, they probably should not be stored in the same table as the entity because each time that value of the attribute is updated, its previous value is lost.

Now we get to business rules. If the business rules that apply to your database indicate that history of sequential attributes is important, then you do need a separate history table. However, if the business rules that apply to your database indicate that history is not required, and that you are only interested in the CURRENT value of any sequential attribute, then you can dispense with the separate history table.

That brings us back to a separate history table for DateofEstablishment, which I believe is a static attribute. SInce the purpose of a history table is to track changes, it doesn't seem to be necessary to have that separate table. You should be able to store the values in the entity table itself.

If you do keep the separate table, of course, that is not really a problem in and of itself. You will have a one-to-one relationship between the tables because each entity will have one and only one corresponding record in the DateofEstablishment table. Keeping it separate may have other advantages to you; I don't know.

Finally, data entry with a separate history table will require a main form/sub form design.


This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.