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

Question

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.

score 1 · Accepted Answer · answered Jul 28 '16 at 18:03

1

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 (& ... &).

answered Jul 28 '16 at 18:03

cxw

16,685
2
45
81

So simple. I thought I tried wrapping it around CStr() before and it didn't work but this time it did. Thank you! – Mekenzie Buhr Jul 28 '16 at 18:17

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

1 Answers1