Solved Dcount record in form after update by Month and year

[theinviter]

I need a help:
i have a form , i need after update clinic, it will Dcount all record from in field "total". so i tried belwo code but still getting error:

Me.total= Nz(DCount("*", "[MRN_number_Of_PO]", "[Location - Clinics Code] =" & Me.Combo2 & " and [Year22] = " & Me.Year1), 0)

Also Dcont per year and month in filed "JAn"

Me.total= Nz(DCount("*", "[MRN_number_Of_PO]", "[Location - Clinics Code] =" & Me.Combo2 & " and [Year22] = " & Me.Year1 and Month = Me.M1), 0)

so can you guide me, i attached picture of the query and form.

thanks

 

[Ranman256]

a count should never be null.
is clinic code a string? Then it’s:

Me.total= DCount("*", "[MRN_number_Of_PO]", "[Location - Clinics Code] =‘" & Me.Combo2 & "‘ and [Year22] = " & Me.Year1)

 

[Minty]

It looks as if you are storing months and years in separate fields rather than as a single date?

That's not a great idea, and I'm also worried about a field called Year22, that also hints at poorly stored data.
Can you post up a picture of your table design?

 

[theinviter]

a count should never be null.
is clinic code a string? Then it’s:

Me.total= DCount("*", "[MRN_number_Of_PO]", "[Location - Clinics Code] =‘" & Me.Combo2 & "‘ and [Year22] = " & Me.Year1)

hi
thanks for helping, but still getting error .

 

[theinviter]

It looks as if you are storing months and years in separate fields rather than as a single date?

That's not a great idea, and I'm also worried about a field called Year22, that also hints at poorly stored data.
Can you post up a picture of your table design?

file attached

 

[Minty]

That's not a table - that's a form, I'd like to see your table's structures please, you can simply open the relationships window to see them all. Take a screenshot and paste it into the thread.

 

[theinviter]

That's not a table - that's a form, I'd like to see your table's structures please, you can simply open the relationships window to see them all. Take a screenshot and paste it into the thread.

please find attached file

 

[theinviter]

i modified to :
DCount("*", "[MRN_number_Of_PO]", "[Location - Clinics Code] =" & Me.Combo2 And [Year22] = Me.Year1)
but still same issue error "type mismatch"

 

[Minty]

please find attached file

Nope that's your query, and I can't see all the fields

I want to see all your tables and all the fields something like this:

These are from someones demo - no shooting the messenger please.

 

[theinviter]

hi:
I figured it out, thanks alot you are amazing team.
this is how i did it;
Me.sum2021 = Nz(DCount("*", "[MRN_number_Of_PO]", "[Location - Clinics Code] =" & Me.Combo2 & " And Year(Date_) = " & [Year1]), 0)

thanks

 

[arnelgp]

you don't need Nz() in your expression.
DCount() will either return 0 or a value.