Dcount help

[rdonges]

Is it not possible to reference the value of a control on a form for the
criteria in a Dcount function? The example in Access' help don't state this
explicitly. If you can only use constants as the criteria in Dcount, then
its usefulness is extremely limited. Here's what I'm trying to do, and it
only returns "0" no matter how many records match the criteria:

Dim numPermits As Integer
numPermits = DCount("[MemberID]", "tblPermits", "[MemberID] =
'frmPermitsMain!cboMemberID.Value'")

The fact that all I get is zeros tells me Dcount thinks my criterion is a
constant, and it isn't even looking at the combo box. Is it possible to use
a reference to a control or to use a variable for the criteria in Dcount?
Any answers or suggestions are greatly appreciated.

Randy Donges

 

[rdonges]

Thanks for the reply. MemberID is a string value, and the function will
work in the following format:

numPermits = DCount("[MemberID]", "tblPermits", "[MemberID] = '" &
Forms![frmPermitsMain]![cboMemberID].Value & "'")


I got this example in another reply and found out that if Forms! isn't
included, an error is returned. Thanks again for replying.

Randy Donges

if MemberID.Value is a string Try

numPermits = DCount("[MemberID]", "tblPermits", "[MemberID] = ' " &
frmPermitsMain!cboMemberID.Value & "'")

if MemberID is a number Try

numPermits = DCount("[MemberID]", "tblPermits", "[MemberID] = " &
frmPermitsMain!cboMemberID.Value )

Ron W

Is it not possible to reference the value of a control on a form for the
criteria in a Dcount function? The example in Access' help don't state this
explicitly. If you can only use constants as the criteria in Dcount, then
its usefulness is extremely limited. Here's what I'm trying to do, and it
only returns "0" no matter how many records match the criteria:

Dim numPermits As Integer
numPermits = DCount("[MemberID]", "tblPermits", "[MemberID] =
'frmPermitsMain!cboMemberID.Value'")

The fact that all I get is zeros tells me Dcount thinks my criterion is a
constant, and it isn't even looking at the combo box. Is it possible to use
a reference to a control or to use a variable for the criteria in Dcount?
Any answers or suggestions are greatly appreciated.

Randy Donges