Microsoft Access VBA - count records with date comparisons

First of all I am still a novice with Microsoft Access VBA and queries.

I am trying to work out how I can count records in a MS Access 2010 table, based on comparing a date field with today's date in an Event Procedure within a form.

Basically I have the field "app date" in a table called "tblMain"and I want to count the number of records in which the "app date" was more than 1 calendar month ago. I am assuming this uses a DCount statement but I'm not sure how to implement this.

Any help would be greatly appreciated.
calibre74Asked:
Who is Participating?
 
Patrick MatthewsCommented:
<smacks own head for forgetting the date adjustment />

DCount("[app date]", "[tblMain]", "[app date] <= #" & DateAdd("m", -1, Date()) & "#")
0
 
Patrick MatthewsCommented:
Try something like this.  Post it in the SQL view, and update as needed with actual table/column names:


SELECT Count([app date]) AS Records
FROM [tblMain]
WHERE [app date] <= DateAdd("m", -1, Date())

Open in new window

0
 
Patrick MatthewsCommented:
As a DCount:



DCount("[app date]", "[tblMain]", "[app date] <= #" & Date() & "#")
0
 
GRayLCommented:
Patrick:  Your DCount doesn't cut it.
0
 
GRayLCommented:
Now you've covered the bases.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.