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