Help correcting syntax for DCOUNT

[Guest]

Using Office 2003 and Windows XP:

I'm trying to obtain the DCOUNT of Job_Titles where they appear in
variables: strDept1 and strDept2; but, I get zero even though I know that at
least one exists.

Dim lCount as Long
lCount = DCount("JOB_TITLE", TableHeadCounts, DEPT1 = " & strDEPT1 & " And
DEPT2 = " & strDEPT2 & " And JOB_TITLE = " & strJOBTITLE & ")

1) Can someone please correct my formula above so it will work?

2) This formula is in a Form's class module - does this matter?

Thanks for the much needed help.

 

[Douglas J. Steele]

Is TableHeadCounts the name of the table, or a variable containing the name
of the table? If it's the table name, you need quotes around it.

Also, from the field names, I'm assumng that Dept1, Dept2 and Job_Title are
Text fields. If so, you need quotes around the values:

Dim lCount as Long

lCount = DCount("JOB_TITLE", "TableHeadCounts", _
"DEPT1 = '" & strDEPT1 & "' And " & _
"DEPT2 = '" & strDEPT2 & "' And " & _
"JOB_TITLE = '" & strJOBTITLE & "'")

Exagerated for clarity, that's

lCount = DCount("JOB_TITLE", "TableHeadCounts", _
"DEPT1 = ' " & strDEPT1 & " ' And " & _
"DEPT2 = ' " & strDEPT2 & " ' And " & _
"JOB_TITLE = ' " & strJOBTITLE & " ' ")

Since you're definitely missing at least one quote in your statement, you
really should have got an error. Have you set

On Error Resume Next

anywhere in your code?