Search code examples
vbams-accesscriteriadcount

Using VBA in Access how do I use Dcount to count the number of record that contain this formula - DatePart("y", Now)?

I want to go through the records in a table to see how many already contain the Julian date of today. I want it to be a wildcard search because my project numbers will be in this format "16-2101". The Julian date is in the middle (210).

My code is:

Private Sub AddProjectNum_Click()

    TwoDigitYear = Mid$(CStr(DatePart("yyyy", Now)), 3, 2)
    dayOfyear = DatePart("y", Now)
    CountofProjectsToday = DCount("[ProjectNumber]", "Table1", "[ProjectNumber] Like '*dayOfyear*'")

    If CountofProjectsToday = 0 Then
        Me.ProjectNum.Value = TwoDigitYear & "-" & dayOfyear & 1
    Else
        Me.ProjectNum.Value = TwoDigitYear & "-" & dayOfyear & CountofProjectsToday + 1
    End If

End Sub

If I were to type the actual Julian date (210) in the place of "dayOfyear" the code works. It doesn't like the reference and I don't know how to get around it.

Solution

  • Try

    CountofProjectsToday = DCount("[ProjectNumber]", "Table1", "[ProjectNumber] Like '*" & CStr(dayOfyear) & "*'")
'                                                                                  ^^^^^^^^^^^^^^^^^^^^^^^

    That converts your VBA dayOfYear into a string (using CStr), then pastes the resulting string into your query (& ... &).