Create Message Box based on query results

[stanley721]

Seems easy, but Im not quite gettng it. I have a form that has a button on it. I want anyone who clicks on the button to be able to enter a year and month that queries the total number of records in the table during that time frame. Then I would like a message box to popup and say "There were X amount of records entered this month." I do not want the resulting query table to be displayed, just a message box.
My sql query is:


SELECT Count(RecordList.TrackingNumber) AS CountOfTrackingNumber
FROM RecordList
GROUP BY Year([EntryDate]), Month([EntryDate])
HAVING (((Year([EntryDate]))=[Enter Year]) AND ((Month([EntryDate]))=[Enter the Month #]))

So now how can I run this query off of clicking a button and then display my count in a message box?

[RuralGuy]

Why not use a DCount() instead? You don't need the GroupBy clause since the whole table will be processed anyway.

[stanley721]

So, use DCount instead of count in the sql query?
Then how do I get that to the Message Box?

[RuralGuy]

DCount() is a stand alone function and does not need to be in a query. It returns an answer all by itself. http://access.mvps.org/access/general/gen0018.htm

[stanley721]

OK, so how do I get that into my message box? I want the user to input which month they need a count of records. I guess I'm getting lost here.

[RuralGuy]

You would construct the DCount() very similar to how you have your SQL but as a WhereCondition clause without the word WHERE. Something like:

SomeVariable = MsgBox("There are " & DCount("[TrackingNumber]","RecordList", & _
"(Year([EntryDate])=[Enter Year]) AND (Month([EntryDate])=[Enter the Month #]))") & _
" records that match that criteria."

WARNING!! Air Code - Not tested!

[stanley721]

Got an error.


Private Sub Command78_Click()
MsgBox("There are " & DCount("[TrackingNumber]","RecordList", & "(Year([EntryDate])=[Enter Year]) AND (Month([EntryDate])=[Enter the Month #]))") & " records that match that criteria."
End Sub

[RuralGuy]

That MsgBox returns a value (which you do not care about but need to allow for. Try:
JunkVariable = Msgbox(...