Validation rule for a month field

[Alliana Gray]

In the forms for my database, I require the user to input a month and year. Due to how the reports are structured, I need the format of the month and year to be very specific. I tried to set the format as 'mmmm yyyy' however even though it is visibly displayed as this, in the table, the values are still stored as sometimes 6/1/2011 or June 2011 and Jun 2011, which in Access' eyes are two different values.

In the report, it will display June multiple times because of this format discrepancy. I am trying to create a validation rule to ensure the user will enter the month and year in the correct format, but I have no idea how to go about writing the expression.

[TheShabz]

Can you provide them, instead of direct entry, a combobox with month names and one with years and then use a query to enter it into your database?

[Alliana Gray]

Yeah I can... thanks for the help! However, can you help me out with something else? I want a message to be displayed if there is already a record with the same Location and Month/Year saying "You are entering duplicate data"

[TheShabz]

Will you be holding that in one field or two?

[Alliana Gray]

Two fields. One field is Location and one field is Month/Year.

[TheShabz]

You can use VBA for this. Using the event trigger of whatever you use to enter the data, you can do something like:

Dim rs as DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblName WHERE Month Name = " & Me.MonthComboBox & " AND YearName = " & Me.YearComboBox

rs.MoveLast

'BOF stands for Beginning of File. EOF Stands for End of File. If it is both the beginning and end then there are no results, which means it is not a duplicate from the query 'above.
If Not (rs.BOF AND rs.EOF)
MsgBox "This Month and Year pairing has already been used. Please try again.", vbOkOnly
Exit Sub
Else
'code to continue or just exit
End If

End Sub

[Alliana Gray]

The month and year are held in the same field. I am trying to display the message when the location and month/year pairing are the same as a previous record.

[TheShabz]

Oh. Just replace the SQL in the OpenRecordset() function to pull the date and the location instead. The rest should be fine.