# SolvedDcount with 2 criteria. (1 Viewer)

#### theinviter

##### Registered User.
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
Try breaking it down. Can you make it work if you only have one criteria?

#### theinviter

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

#### bob fitz

##### AWF VIP
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.
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
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.

Replies
11
Views
404
Solved Date between
Replies
17
Views
223
Replies
4
Views
192
Replies
6
Views
422
Replies
7
Views
553