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

Re-Formatting Finding Aids for Conversion to EAD: Parsing the Date From the Title

Quite often our legacy finding aids have the date at the end of the title field, rather than as in a separate data field. For Ead McTaggart to properly format the EAD guides, I need the year, or range of years to be in a separate date field. This means I need a way to move the year from the title field to the date field without copying and pasting hundreds, often thousands, of times per finding aid. I automated this process using an if-then-else statement.

Fortunately the dates in most of our findings conform to a few variations of a few formats: they end in a single four-digit year (i.e. 1984), year range (i.e. 1920-1932 or 1920-32); or have the elements of the date separated by slashes (i.e. 3/25/1911 or 3/25/11).

If we assume the first title is in cell C2 then if the date ends in a four-digit year I just want the last four digits. To determine if this is the case the logic test of the if-then-else statement looks to see if the fifth character from the right is a space or a slash:

= IF((MID(C2,LEN(C2)-4,1)= " ")+(MID(C2,LEN(C2)-4,1)= "/"),RIGHT(C2,4),…)

If the date looks like 1920-1932 then the fifth character from the right is a dash and I want the last nine characters:

=IF(MID(C2,LEN(C2)-4,1)= "-",RIGHT(C2,9),…)

But if the date looks like 1920-32 then the third character from the right is a dash and I not only want the last seven characters, but I also want to insert “19” immediately after the dash:

=IF(MID(C2,LEN(C2)-2,1)="-",MID(C2,LEN(C2)-6,5)&"19"&RIGHT(C2,2),…)

That just leaves the dates separated by slashes with only the last two digits of the year. In this case the third character from the right is a slash and I want the last two characters with “19” inserted in front of them:

=IF(MID(C2,LEN(C2)-2,1)="/","19"&RIGHT(C2,2),…)

This last test will actually have to be the first test for those times when the date is just the month and the year (i.e. 5/14), otherwise the formula will see that the fifth character from the right is a space and pull the last four characters before it sees that the third character from the right is a slash.

And if none of the above conditions are met then there is no date at the end of the title and I want nothing in the title field so I set the final false value to a zero length string: "".

Here is the complete formula:

= IF(MID(C2,LEN(C2)-2,1)="/","19"&RIGHT(C2,2), IF((MID(C2,LEN(C2)-4,1)= " ")+(MID(C2,LEN(C2)-4,1)= "/"),RIGHT(C2,4),IF(MID(C2,LEN(C2)-4,1)= "-",RIGHT(C2,9),IF(MID(C2,LEN(C2)-2,1)="-",MID(C2,LEN(C2)-6,5)&"19"&RIGHT(C2,2), ""))))

There will, of course be some outliers: oddly formatted dates, and so on. Still this formula will work for the vast majority of the dates I see in our finding aids. Proof-reading picks up the outliers.