Limit date entered to 1st or 15th on a form

[TinaR]

I'm using Access 2007. I have a date field where the only date that should
be entered is either the 1st or 15th of the month. Is it possible to limit
data entry to one or the other? So if the user enters the 14th, they get an
error message?

Thanks in advance.
Tina

 

[Douglas J. Steele]

Put logic in the text box's BeforeUpdate event:

Private Sub MyDateField_BeforeUpdate(Cancel As Integer)

If Day(Me!MyDateField) <> 1 And Day(Me!MyDateField) <> 15 Then
MsgBox "The date must be either the 1st or the 15 of the month"
Cancel = True
End If

End Sub

Of course, a friendlier option would be to change the value for them...

 

[Mr. B]

You can limit your data input using the Validation Rule and the Validation
Text properties of the control where the use will type in the value.

In the "Validation Rule" property use:
="1" Or "15"

In the "Validation Text" property provide the message you want th user to see.
Like:
You must enter a "1" or "15"

You might also consider providing a combo box with only values of 1 and 15.
Set the Limit To List property to true. This will prevent the user from have
any other value in that field.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Douglas J. Steele]

If the input is a date, setting a validation rule of ="1" Or "15" won't do
anything.

 

[Jerry Whittle]

Assuming that the field is named TheDate, this will work as a field
validation rule in the table.

Day([TheDate]) In (1,15)

The validation test could be something like "The date must be either the 1st
or 15th."

 

[John W. Vinson]

I'm using Access 2007. I have a date field where the only date that should
be entered is either the 1st or 15th of the month. Is it possible to limit
data entry to one or the other? So if the user enters the 14th, they get an
error message?

Thanks in advance.
Tina

Rather than letting the user enter any date they like, and slapping their hand
if they enter a wrong one, would it perhaps be better to just let the user
select a valid date, say from a combo box, or to automatically convert any
date before the 15th to the first of the month, and any date after to the
15th?

 

[Mr. B]

Sorry, I may have assumed too much. I took it that the OP just wanted to get
the 1 or the 15 and would handle eveything else. Thanks for your comments.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[TinaR]

This works great! Thank you so much for the quick reply.

BTW, I usually do use the friendlier approach but in this case, my queries
pull reports for these dates only. This way if the 1st or 15th fall on a
weekend, I don't need to first look at a calendar to figure out the Friday or
Monday date that I need to enter into the query. Thanks again!

 

[TinaR]

This also works great. Thank you for another option!

Assuming that the field is named TheDate, this will work as a field
validation rule in the table.

Day([TheDate]) In (1,15)

The validation test could be something like "The date must be either the 1st
or 15th."
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm using Access 2007. I have a date field where the only date that should
be entered is either the 1st or 15th of the month. Is it possible to limit
data entry to one or the other? So if the user enters the 14th, they get an
error message?

Thanks in advance.
Tina