Validating a Year Field in MS Access

For my folder list database, which is where students enter basic folder level data for our finding aids, I only want a four-digit year or two four-digit years separated by a dash in the data field. The reason for this is that programming that runs against the folder list later relies on that format for the date. I have been having a problem with students remembering this and decided to force them to comply via programming in the database.

My first thought was to create a validation rule for the field in the table, which would be:

Like “####” Or Like “####-####”

But I wanted to stop the OnClick event and return the user to the date field on the form while being certain that nothing was saved to the table (I use unbound controls on my forms).

Adding the validation rule to the date text box would accomplish this, but I need to add “Is Null” to the rule or Access would not allow a folder with no date, of which we have many:

Like "####" Or Like "####-####" Or Is Null

The one thing I did not like about this was that if the date in the text box was invalid you could not use any of the other controls on the form until you fixed the date. I can see wanting to use the “Close” or “Clear” buttons. So what I opted to do was put the validation in the VBA for the OnClick event behind the Save button.

Here is the code I inserted right after the value from the Date textbox is picked up:

     If strDate Like "####" Then

          'The date is a four-digit year,

          'continue with the OnClick event.

     ElseIf strDate Like "####-####" Then

          'The date is two four-digit years separated by a dash,

          'continue with the OnClick event.

     ElseIf strDate Like "" Then

          'No date, which I consider okay,

          'continue with the Onclick event.

     Else:

          MsgBox ("Please enter a 4-digit year or two 4-digit years separated by a dash")

          'The date is invalid, set focus to the date field and stop the OnClick event.

          Me.txtDate.SetFocus

          Exit Sub

     End If

Single Year and Inclusive Date Range (Year’s Only) Searching in MS Access

In our publications database the dates are enter only as a single four-digit year, or a range of years (two four-digit years separated by a dash). I wanted researchers to get all items that match their date limits, so a simple match on the date field would not work. For example if someone puts 1932 as the date search they should get not only the items with a date of 1932, but also the inclusive dates of 1932-1945 or 1924-1932 or 1910-1950. Conversely, a search for 1961-1970 should return not just 1961-1970, but also 1950-1963 and 1967-1980 and 1965-1966 and 1968. You get the picture.

This actually turns out to be fairly simple, requiring only three clauses in an SQL query.

First the search term needs to be parsed into two separate dates: a start date and an end date.

     strSearchDate = Nz(Me.txtSearch, "")

     strStartDate = Left(strSearchDate,4)

     strEndDate = Right(strSearchDate,4)

Of course, if the search date is a single four-digit year then strStartDate and strEndDate will be the same, but that doesn’t matter.

The first clause pulls any items where if the search date is a single year the item date is an exact match or if the search date is an inclusive date the item date is either a single year or an inclusive date entirely within the search date:

     (LEFT(tblPams.Date,4)>='" & strDateStart & "' AND RIGHT(tblPams.Date,4)<= '" & strDateEnd & "') 

So if someone searches for 1944 this clause would return only 1944, if the search date is 1944-1948 it would return 1945 and 1944-1946 and 1945-1947.

Next we want to find any items where the beginning of an inclusive-date search falls within its inclusive date range:

     '" & strDateStart & "' BETWEEN LEFT(tblPams.Date,4) AND RIGHT(tblPams.Date,4) 

So if the search date is 1922-1933 it will return 1919-1924. Last we want to find any items where the end of the inclusive-date search falls within its inclusive date range:

     '" & strDateEnd & "' BETWEEN LEFT(tblPams.Date,4) AND RIGHT(tblPams.Date,4)

So if the search date is 1922-1933 it will return 1930-1940.

Here is the full VBA code you would need behind a date search button:

Private Sub cmdDateSearch_Click() 

On Error GoTo Err_cmdDateSearch_Click 

     Dim strSearchDate As String      'Variable to hold the search date. 

     Dim strSQL As String                'Variable to hold the SQL query. 

     Dim strDateStart As String         'Variable to hold the start date of the search date. 

     Dim strDateEnd As String          'Variable to hold the end date of the search date. 

     'Get the search date from the form and parse it into the start date and the end date. 

     strSearchDate = Nz(Me!txtDateSearch, "") 

     'Debug.Print strSearchDate 

     strDateStart = Left(strSearchDate, 4) 

     'Debug.Print "Start date = " & strDateStart 

     strDateEnd = Right(strSearchDate, 4) 

     'Debug.Print "End date = " & strDateEnd 

     'This is the SQL query used to populate the results list. 

     strSQL = "SELECT tblPams.PamID, tblPams.Title, tblPams.Date FROM tblPams " 

     strSQL = strSQL & "WHERE ((LEFT(tblPams.Date,4)>='" & strDateStart & "' AND RIGHT(tblPams.Date,4)<= '" & strDateEnd & "') "

     strSQL = strSQL & "OR ('" & strDateStart & "' BETWEEN LEFT(tblPams.Date,4) AND RIGHT(tblPams.Date,4) "

     strSQL = strSQL & "OR '" & strDateEnd & "' BETWEEN LEFT(tblPams.Date,4) AND RIGHT(tblPams.Date,4))) "

     strSQL = strSQL & "ORDER BY Article(tblPams.Title), tblPams.Date;"

     'Debug.Print strSQL

     'Populate the results list and refresh the form.

     Me.lstPamList.RowSource = strSQL

     Forms!frmStartPage.Refresh

     'Clear the search box.

     Me!txtSearchDate = Null

Exit_cmdDateSearch_Click:

Exit Sub

Err_cmdDateSearch_Click:

MsgBox Err.Description

Resume Exit_cmdDateSearch_Click

End Sub