DCount Help (1 Viewer)

Jazmine88

New member
Local time
Today, 07:29
Joined
Oct 21, 2021
Messages
12
Hi,

I have been trying to put a DCount function into a textbox so it will count how many meetings have happened this year, so far I can have it count the total amount of meetings but when I add in the criteria it comes up with #Error.

The syntax I am using is
=DCount("ID","Meetings in 2021","Date of Meeting = #*2021#")

Can anyone advise me on where I am going wrong?
Thank you
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:29
Joined
Sep 21, 2011
Messages
9,596
You would need Like if you are going to use *
Could just use Year() of the Date of Meeting?
 

Minty

AWF VIP
Local time
Today, 07:29
Joined
Jul 26, 2013
Messages
9,074
You can't use a wild card in the date criteria like that.
You would need to use the Year() function.

Edit : The Gman beat me to it...
 

Jazmine88

New member
Local time
Today, 07:29
Joined
Oct 21, 2021
Messages
12
Hi, thank you for your replies, I have tried using Year() as well in the code but it still comes back as a #Error
 

Minty

AWF VIP
Local time
Today, 07:29
Joined
Jul 26, 2013
Messages
9,074
So what did you try? It should be something like
=DCount("ID","Meetings in 2021","Year([Date of Meeting]) = 2021")

This will mean that every year you need to adjust this, for 2 reasons - your query name should change, and the year is hard-coded.
There are much better ways to achieve this.

If you simply queried your meetings table rather than a query hard-coded to the year you could do
Code:
=DCount("ID","YourMeetingsTable","Year([Date of Meeting]) = Year(Date())")

Which would always return the meetings for the current year...
When something doesn't work, and you try something else, it always helps to show us the latest code/expression you are using.
 

Jazmine88

New member
Local time
Today, 07:29
Joined
Oct 21, 2021
Messages
12
So what did you try? It should be something like
=DCount("ID","Meetings in 2021","Year([Date of Meeting]) = 2021")

This will mean that every year you need to adjust this, for 2 reasons - your query name should change, and the year is hard-coded.
There are much better ways to achieve this.

If you simply queried your meetings table rather than a query hard-coded to the year you could do
Code:
=DCount("ID","YourMeetingsTable","Year([Date of Meeting]) = Year(Date())")

Which would always return the meetings for the current year...
When something doesn't work, and you try something else, it always helps to show us the latest code/expression you are using.
Hi Minty

This is great, thank you. I wanted to hard code the year in because I am doing separate text boxes for each year of meetings so I have now put in =DCount("ID","Meetings in 2021","Year([Date of Meeting]) = 2021") and it works!! I think I got a bit confused with the Year part as I was not sure where this should go in the code.

Thank you so much for this 🙂
 

Minty

AWF VIP
Local time
Today, 07:29
Joined
Jul 26, 2013
Messages
9,074
You could do this automatically for the last 3 (or 5 or 10 years) years, and adjust the labels for the controls as well;

Current year =DCount("ID","YourMeetingsTable","Year([Date of Meeting]) = Year(Date())")
Last year =DCount("ID","YourMeetingsTable","Year([Date of Meeting]) = Year(Date()) -1")
Year before that =DCount("ID","YourMeetingsTable","Year([Date of Meeting]) = Year(Date()) -2")

The labels could be set simply in the form load event.
Code:
Me.LabelLastYear.Caption  = "Meetings in " & Year(Date()) -1
This means your form is not hard coded at all.
 

Jazmine88

New member
Local time
Today, 07:29
Joined
Oct 21, 2021
Messages
12
You could do this automatically for the last 3 (or 5 or 10 years) years, and adjust the labels for the controls as well;

Current year =DCount("ID","YourMeetingsTable","Year([Date of Meeting]) = Year(Date())")
Last year =DCount("ID","YourMeetingsTable","Year([Date of Meeting]) = Year(Date()) -1")
Year before that =DCount("ID","YourMeetingsTable","Year([Date of Meeting]) = Year(Date()) -2")

The labels could be set simply in the form load event.
Code:
Me.LabelLastYear.Caption  = "Meetings in " & Year(Date()) -1
This means your form is not hard coded at all.
Hi that’s great, thank you, I will try this so I can avoid hard coding 🙂
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:29
Joined
Feb 19, 2002
Messages
34,462
I wanted to hard code the year in because I am doing separate text boxes for each year of meetings
Hard coding things like this is always a poor choice. Try Minty's suggestion instead.
 

Users who are viewing this thread

Top Bottom