Solved Dcount with 2 criteria. (1 Viewer)

theinviter

Registered User.
Local time
Today, 09:36
Joined
Aug 14, 2014
Messages
168
Dear guys;
need help,
i have a form to count the number in record by 2 criteria.
i tried this but not work,

Me.Text186 = Nz(DCount("*", "[MRN]", "[Location - Clinics Name] ='" & Me.Combo2 & "'" & "Date_ BETWEEN DateValue('" & Me.From & "') AND DateValue('" & Me.to_ & "') ), 0)

so what to do.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:36
Joined
Oct 29, 2018
Messages
18,989
Try breaking it down. Can you make it work if you only have one criteria?
 

theinviter

Registered User.
Local time
Today, 09:36
Joined
Aug 14, 2014
Messages
168
tried tis but still got syntax error
Text17 = Nz(DCount("*", "[MRN]", "[Date_] between #" & [Dates_From] & "# _And #" & [Dates_To] & "#"), 0)
 

bob fitz

AWF VIP
Local time
Today, 17:36
Joined
May 23, 2011
Messages
4,444
Text17 = Nz(DCount("*", "[MRN]", "[Date_] between #" & [Dates_From] & "# _And #" & [Dates_To] & "#"), 0)
Perhaps that should be
Text17 = Nz(DCount("*", "[MRN]", "[Date_] between #" & [Dates_From] & "# And #" & [Dates_To] & "#"), 0)
 

theinviter

Registered User.
Local time
Today, 09:36
Joined
Aug 14, 2014
Messages
168
Text17 = Nz(DCount("*", "[MRN]", "[Date_] between #" & [Dates_From] & "# _And #" & [Dates_To] & "#"), 0)
Perhaps that should be
Text17 = Nz(DCount("*", "[MRN]", "[Date_] between #" & [Dates_From] & "# And #" & [Dates_To] & "#"), 0)
thanks alot
but how to add the second criteria [Location - Clinics Name]
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:36
Joined
Feb 19, 2002
Messages
36,342
Me.Text186 = Nz(DCount("*", "[MRN]", "[Location - Clinics Name] ='" & Me.Combo2 & "'" & "Date_ BETWEEN DateValue('" & Me.From & "') AND DateValue('" & Me.to_ & "') ), 0)

Dim strWHERE
strWHERE = "[Location - Clinics Name] ='" & Me.Combo2 & "' AND Date BETWEEN DateValue('" & Me.From & "') AND DateValue('" & Me.to & "'"
Me.Text186 = Nz(DCount("*", "[MRN]", strWHERE) ), 0)

it is easier for debugging if you build complex strings in a variable. That way, you can put a stop in the code and print out the value of the variable to see if it is correct.

FYI - names should contain only letters, numbers, and the underscore. NO embeded spaces or special characters. AND you need to avoid using the names of functions and properties as column names. Date is the name of a function. You will have trouble in code because Access won't know if you are talking about YOUR Date or the Date function. And finally, Text186 is not appropriate. When you create a control, access just assigns it the next sequential number. it is up to YOU to change the name property to a rational name.
 

Users who are viewing this thread

Top Bottom