Search code examples
datems-accessvbams-access-2016

How to retrieve a count of all records where a Date field is within the next year, and store it in a variable?

I am fairly new to MS Access, but have a decent understanding of databases, with some knowledge of SQL.

I am creating a database in Access. On a main form that users will see first, I need to display a count of all records from my Case table, which have a StatuteOfLimitation date that is within the next year.

My goal was to create a label describing the information, with a button below it. The button will open a report of all of the records (this part is working fine), and I wanted the caption for the button to display the total count of how many records meet the criteria.

The only way I can think to do it, is to retrieve the count and store it into a variable. From there, I should be able to set the caption to the variable value.

I have seen a few methods of retrieving a count and storing it in a variable, but all that I found only stored a count of EVERY record, without filtering for the date range.

This was the best that I could think of, but it is not working:

Private Sub Form_Load()

    Dim oneYearFromToday As TempVars
    SET TempVars!oneYearFromToday = (SELECT COUNT(StatuteOfLimitation) FROM Case 
    WHERE StatuteOfLimitation <= DateAdd("yyyy", 1, Date());

End Sub
Solution

  • DCount() provides a simple approach for "How to retrieve a count of all records where a Date field is within the next year"

    Dim lngCount As Long
lngCount = DCount("*", "Case", "[StatuteOfLimitation] <= DateAdd('yyyy', 1, Date())")

    Then you can use that count in your command button's Caption property. Say the button is named cmdOpenReport ...

    Me!cmdOpenReport.Caption = "Report " & lngCount & " cases"

    If you want the count in a TempVar instead of a regular Long variable, declare it As TempVar (just one) instead of As TempVars (a collection). And when you assign the value to it, don't use Set.

    Dim oneYearFromToday As TempVar
TempVars!oneYearFromToday = DCount("*", "Case", "[StatuteOfLimitation] <= DateAdd('yyyy', 1, Date())")