Link to home
Start Free TrialLog in
Avatar of foxpc123
foxpc123

asked on

Dcount on multiple criteria


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.

Avatar of als315
als315
Flag of Russian Federation image

"[week] =" & thisweek & "And [CompanyID]=" & thisCompany
Add some spaces:
"[week] = " & thisweek & " And [CompanyID] = " & thisCompany
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
JobCount = DCount("[Week]", "tmpJobs", "[week] ='" & thisweek And "'[CompanyID]='" & thisCompany & "'")
JobCount = DCount("[Week]", "tmpJobs", "[week] ='" & thisweek  "' And [CompanyID]='" & thisCompany & "'")
Avatar of foxpc123
foxpc123

ASKER

Thanks for quick responses