Help correcting syntax for DCOUNT

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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?
 
Back
Top