Dcount on multiple criteria

foxpc123
foxpc123 used Ask the Experts™
on

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.

Comment
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
Commented:
try:

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] = "

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

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

Author

Commented:
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