DCount Help (1 Viewer)

[Jazmine88]

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]

You would need Like if you are going to use *
Could just use Year() of the Date of Meeting?

 

[Minty]

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]

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]

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

=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]

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

=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]

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.

Me.LabelLastYear.Caption  = "Meetings in " & Year(Date()) -1

This means your form is not hard coded at all.

 

[Jazmine88]

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.

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]

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.

 

[Jazmine88]

Hard coding things like this is always a poor choice. Try Minty's suggestion instead.

I will do, thank you