Problem using Dcount

  • Thread starter Thread starter Jess Wundring
  • Start date Start date
J

Jess Wundring

I'm trying to use dcount to give me the count of a subset of records. It's
always returning a count of ALL the records in the table.

For example,

myCount = DCount("[caseid]", "tblCaseCharges", """[caseid] = '" & ID &
"'""")
MsgBox "myCount = " & myCount & " and " & """[caseid] = '" & ID & "'"""

displays this in the message box:

myCount = 7 and "[caseid] = '8"'

where 7 is actually the TOTAL number of records in the table. FWIW, caseid
is defined as a long int. I've been banging my head against the wall (and the
keyboard) on this. Please help if you can...

And Thanks
 
Jess Wundring said:
I'm trying to use dcount to give me the count of a subset of records. It's
always returning a count of ALL the records in the table.

For example,

myCount = DCount("[caseid]", "tblCaseCharges", """[caseid] = '" & ID &
"'""")
MsgBox "myCount = " & myCount & " and " & """[caseid] = '" & ID & "'"""

displays this in the message box:

myCount = 7 and "[caseid] = '8"'

where 7 is actually the TOTAL number of records in the table. FWIW, caseid
is defined as a long int. I've been banging my head against the wall (and
the
keyboard) on this. Please help if you can...

And Thanks
Click to expand...

The problem is you're treating ID as a string. Removing the quotes should do
the trick:

myCount = DCount("[caseid]", "tblCaseCharges", "[caseid] = " & ID

MsgBox "myCount = " & myCount & " and " & "[caseid] = " & ID
 
Thanks, Stuart.

I thought I had tried that originally, but I must have had some other
problem because it does work. Whew! I'm glad I'm finally past this barrier.
 
Jess Wundring said:
I'm trying to use dcount to give me the count of a subset of records. It's
always returning a count of ALL the records in the table.

For example,

myCount = DCount("[caseid]", "tblCaseCharges", """[caseid] = '" & ID &
"'""")
MsgBox "myCount = " & myCount & " and " & """[caseid] = '" & ID & "'"""

displays this in the message box:

myCount = 7 and "[caseid] = '8"'

where 7 is actually the TOTAL number of records in the table. FWIW, caseid
is defined as a long int. I've been banging my head against the wall (and
the
keyboard) on this. Please help if you can...

And Thanks
Click to expand...

The problem is you're treating ID as a string. Removing the quotes should do
the trick:

myCount = DCount("[caseid]", "tblCaseCharges", "[caseid] = " & ID

MsgBox "myCount = " & myCount & " and " & "[caseid] = " & ID
 
Back
Top