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"

 

[Pat Hartman]

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

Embedding a string inside a string requires that you understand what you are doing. The WHERE part of the dCount() is a string: and strings are enclosed within quotes. You are also embedding a string within that string since you are concatenating several elements such as the column name, the relational operator, concatenation operator, and the string value. It is usually easier to get your head around if you build the string part into a separate variable. This also lets you stop the code and easily print out the string so you can see what is wrong with it.

Dim strWhere As String

strWhere = "[Location - Clinics Code] =" & Me.Combo2 & " And [Year22] = " & Me.Year1

DCount("*", "[MRN_number_Of_PO]", strWhere)

If combo2 (ick) is a string, it needs to be encased in single or double quotes

PS , discipline in how you name your objects will save you hundreds of hours over your career as a developer. Understanding normalization will save much more. When a field name contains data as Year22 does, your table is not properly normalized. It is a spreadsheet rather than a relational table and you would be much happier working with Excel than with Access.

 

[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.

 

[Pat Hartman]

Or, you could see if you can fix your syntax.

 

[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.

 

[Pat Hartman]

You might want to read #9 again. And then consider the hole you are digging for yourself by naming fields with data. How many Yearxxxx fields will you need to create? How many forms/reports/queries and code will you need to modify?