How to edit an Input Mask?

[pswilliams0]

Hi all,
I have an input mask in a form field for recording a date. I currently have it set to: 00\->L<LL"-20"00;0;_

This records the format in the "medium date" format of DD-MMM-YY or 15-May-12.

The input mask shows the "20" of 2012 as you enter the characters for the first time, but when you exit the form field, displays as the Medium Date format. This works splendidly.

What doesn't work, is if you need to change the day. If you go back into this form field, and change the day from say 15 to 16 of May, then you'll get an error, until you go and type the "20" even though it isn't actually displayed in the final format.

So, is there a way to:
1. have the "20" automatically show up every time you enter the box to make any kind of edit?
2. have VBA recognize that it is an edit and to just record the data as typed?
3. I'm open to a better way (VBA) to accomplish the clarity of entering the proper date format, but the date format must remain in either DD-MMM-YY or DD-MMM-YYYY.

I'd like to have this work for the clarity of the date format which is what must remain constant. Any help is much appreciated.

Thanks,
Philip

 

[bob fitz]

If you leave the Format property setting blank it may work.

 

[pswilliams0]

Hi, thanks for the thought! Yes, that might work, however, I have VBA calculating future dates from the date entered.

So if I left the format blank, Access would likely not know that it was a date to calculate the other dates.

Philip

 

[bob fitz]

Hi, thanks for the thought! Yes, that might work, however, I have VBA calculating future dates from the date entered.

So if I left the format blank, Access would likely not know that it was a date to calculate the other dates.

Philip

I could be wrong but I think Access would know. I believe that the Format property only affects how the actual data is displayed.

 

[Pat Hartman]

I really dislike input masks for dates. I think they make the data entry harder rather than easier. They certainly annoy me. Access makes assumptions when you enter a date that can reduce a date entry to 3-4 keystrokes as long as the date is in the current year. If the year is different than the current year, then there is no alternative but to enter it. Access is pretty smart but it is not prescient

As long as the control is bound to a date field, Access will only allow valid dates to be entered. If the control is unbound, Access will also restrict entry to valid dates if you have set a date format for the control. If you have business rules regarding the date, you need to put them in the control's BeforeUpdate event or in the Form's BeforeUpdate event if you need to check for nulls or validate against another date.

 

[pswilliams0]

Hi Pat,
That sounds great. When you say I need to include them in the VBA event, what am I actually including? Could you possibly have an example to provide? In a perfect world, the dates would always be DDMMMYYYY -- However this isn't an option in access from the list of date formats.

Thanks,
Philip

 

[Pat Hartman]

The date formats presented should be derived from your system date format. Medium date is dd-mmm-yyyy on my system.

The type of edit I was refering to is where one date is dependent on the value of another date for example. This edit would go in the FORM's BeforeUpdate event and you would cancel the update if an error is found.

If Me.SellDate < Me.PurchaseDate Then
    MsgBox "Sell Date must be >= Purchase Date.",vbokOnly
    Cancel = True
    Exit Sub
End If