Solved Dcount with 2 criteria. (1 Viewer)

[theinviter]

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]

Try breaking it down. Can you make it work if you only have one criteria?

 

[theinviter]

tried tis but still got syntax error
Text17 = Nz(DCount("*", "[MRN]", "[Date_] between #" & [Dates_From] & "# _And #" & [Dates_To] & "#"), 0)

 

[bob fitz]

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]

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]

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.