Dcount on multiple criteria

foxpc123 used Ask the Experts™

I am using the following code to determine if a record exists in table tmpJobs were week = thisweek and CustomerID = thisCustomerID

Open in new window

Dim thisCompany As Integer
Dim thisweek As Integer
JobCount = DCount("[Week]", "tmpJobs", "[week] =" & thisweek And "[CompanyID]=" & thisCompany)

Open in new window

I am getting data mismatch.

If I seperate the line into two

Open in new window

JobCount = DCount("[Week]", "tmpJobs", "[week] =" & thisweek)
NoJobs = DCount("[Week]", "tmpJobs", "[CompanyID]=" & thisCompany)

Open in new window

this works and JobCount and NoJobs = 1

Why can't I  have them on same line to only count when both criteria are met.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"[week] =" & thisweek & "And [CompanyID]=" & thisCompany
Add some spaces:
"[week] = " & thisweek & " And [CompanyID] = " & thisCompany
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

JobCount = DCOUNT("[Week]", "tmpJobs", "[Week] = " & thisweek & " AND [CompanyID] = " & thisCompany)

Not that I have moved the " that was just to the left of [CompanyID] to includ the " AND [CompanyID] = "

JobCount = DCount("[Week]", "tmpJobs", "[week] ='" & thisweek And "'[CompanyID]='" & thisCompany & "'")

JobCount = DCount("[Week]", "tmpJobs", "[week] ='" & thisweek  "' And [CompanyID]='" & thisCompany & "'")


Thanks for quick responses

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial