Limiting entry in date field to three dates

[cmwpcol]

I have limited knowledge with Access especially with creating expressions and macros.

I am looking for help on setting up a validation rule in a date field that will limit the entry to three specific dates.

The field is: [Expiration Date]. The Data Type is: Date/Time The format is: Short Date (m/d/yyyy)

I want to limit the entry in this field to: 1/1, 2/1, or 4/1 with the ability to enter the applicable year.

Any assistance with this would be greatly appreciated.


Thank you,


cmwpcol

[Jrbeene86]

Hey


I don't know any short solutions, but...


you could create two combo boxes and limit the row source to a value list and put "1/1"; "2/1"; etc
The second combobox would contain "11";"12";"13"; etc.

then put an After update event on the second box to requery a third. This third box would be the control source and set up as concatenated field with the default value being something like [text1] & [text2]. (Althought I think you have to type something in to get the combobox value.)

Format this as a short date and it will behave a date.

[orange]

Where would the data be entered and by whom?
I recommend you put a edit/validation routine on the Form involved.

Can you tell us more about the requirement?

[cmwpcol]

The data is entered in the Expiration Date field that exists in a Form by our entry clerks.

My thought is a validation routine is the way to go. However, I have never set up a validation rule. Not to mention, one with a variable which would be the year.

For example, when the entry clerk enters a date of: 9/18/2013. I am looking for a reminder message to pop up saying: "The only dates allowed are 1/1, 4/1, or 2/1." If they enter a date of: 1/1/2013. A reminder message is not necessary. They can proceed to the next field in the form.

I need the date they actually enter to include the applicable expiration year (which could be current year +1 (2013) or current year +2 (2014), etc.).

I currently have a reminder line under this field in the form stating Only Applicable Dates: 1/1 or 4/1 (ASSG) or 2/1 (DSG).However, the entry clerks continually overlook this message as if it doesn't exist. I need to do something to force them to recognize these dates (month and day) are the only acceptable dates.

I appreciate any suggestions you may have.

Thank you!

[cmwpcol]

I'll give this a try.

Thank you!!

[orange]

If you only want the clerks to select certain values, then why not put those value in a combo box on the form, and Limit them to the List?

When I suggested a validation routine, I was talking about a small vba routine to ensure the data met your criteria.
But, as above, if you have only a few valid dates, put them in a combo.