DCount not giving expected result

G

Graham

Looking to use DCount to return a count of specific values - but, what I
appear to be getting is a count of Non Null values.

When I hard code the criteria such as "[CommLearnID] = 8" then the result is
correct - I have spent hours on this - so frustrating.

Code follows

Option Base 1
Option Compare Database
Option Explicit
Dim iMonth, iYear As Integer
Dim sDateCriteria, sSQL As String
Dim aiCount(50) As Integer
Dim asField, asTable As String


Private Sub btnGo_Click()
Dim iCount, iCount2, iField, iLastVal As Integer
Dim sCriteria, sField As String

asField = Array("CommLearnID", "EnqID", "RefFromID", "RefToID")
'asTable = Array("tContacts", "tcCommLearn", "tcEnqType", "tcRefList",
"tcRefList")

sDateCriteria = "[ContactDate] >= #" & DateSerial(iYear, iMonth, 1) & "#
And [ContactDate] < #" _
& DateSerial(iYear, iMonth + 1, 1) & "#"
sSQL = "DELETE tContactStats.* " _
& "FROM tContactStats " _
& "WHERE (tContactStats.CalMth = " & iMonth & " And
tContactStats.CalYr = " & iYear & ");"
'MsgBox iMonth & " | " & iYear
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

For iField = 1 To 4
iLastVal = 0
sField = "[" & asField(iField) & "]"
For iCount = 1 To 50
aiCount(iCount) = 0
Next iCount

For iCount = 1 To 50
'sCriteria = "'" & sDateCriteria & " And " & sField & " = " &
iCount & "'"
sCriteria = """" & sField & " = " & iCount & """"

aiCount(iCount) = DCount("[ContactID]", "tContacts", sCriteria)
'MsgBox sCriteria & " | " & iCount & " | " & aiCount(iCount)
If aiCount(iCount) > 0 _
Then
iLastVal = iCount
End If
Next iCount
If iLastVal > 0 _
Then
For iCount2 = 1 To iLastVal
If aiCount(iCount2) > 0 _
Then
sSQL = "INSERT INTO tContactStats (Identifier, ID,
CalYr, CalMth, Count) " _
& "VALUES ('" & asField(iField) & "', " &
iCount2 & ", " & iYear & ", " & iMonth & ", " & aiCount(iCount2) & ");"
'MsgBox sSQL
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

End If
Next iCount2
End If
Next iField
End Sub

Thanks in advance. This forum is invaluable, particularlt for a casual
Access developer.
(Access 2002)
 
T

Tim Ferguson

Looking to use DCount to return a count of specific values - but, what
I appear to be getting is a count of Non Null values.
Click to expand...

I'm afraid I don't really understand this. If you need a number of
records with non-empty fields, then the criterion has to be something
like
MyField IS NOT NULL
When I hard code the criteria such as "[CommLearnID] = 8" then the
result is correct
Click to expand...

As you would expect...
Code follows
Click to expand...

Sorry, I don't have the time to read some sixty or so lines of
uncommented code... try a MUCH smaller fragment that is giving you the
problem.

I can't actually find a DCount() call in your code anyway.

By the way,
Dim iMonth, iYear As Integer
Dim sDateCriteria, sSQL As String
Dim aiCount(50) As Integer
Dim asField, asTable As String
Click to expand...

You do know that this dimensions iMonth, sDateCriteria, asField as a
Variants, don't you? This is not dot-Net.

Best wishes


Tim F
 
D

Douglas J. Steele

What is your computer's Short Date format set to in Regional Settings?

If it's dd/mm/yyyy, try:

sDateCriteria = "[ContactDate] >= " & _
Format(DateSerial(iYear, iMonth, 1), "\#mm\/dd\/yyyy\#") & _
" And [ContactDate] < " & _
Format(DateSerial(iYear, iMonth + 1, 1), "\#mm\/dd\/yyyy\#")
 
G

Graham

Thanks Doug

I wiil file that away. But at this time, the date part appears to be working
fine on the small set of test data.

The issue is with the dynamic creation of the criteria for DCount - I did
originally include all of the code for completeness.

The following gives the expected result
aiCount(iCount) = DCount("[ContactID]", "tContacts", "[CommLearnID] = 8")

But when I build the criteria dynamically is when I seem to get a count of
non Null values - folling is pertinent extracts from original post
asField = Array("CommLearnID", "EnqID", "RefFromID", "RefToID")
sField = "[" & asField(iField) & "]" - the first iteration returns CommLearnID
For iCount = 1 To 10
sCriteria = """" & sField & " = " & iCount & """"
aiCount(iCount) = DCount("[ContactID]", "tContacts", sCriteria)
MsgBox sCriteria & " | " & iCount & " | " & aiCount(iCount)
Next iCount
Thanks
--
Graham


Douglas J. Steele said:
What is your computer's Short Date format set to in Regional Settings?

If it's dd/mm/yyyy, try:

sDateCriteria = "[ContactDate] >= " & _
Format(DateSerial(iYear, iMonth, 1), "\#mm\/dd\/yyyy\#") & _
" And [ContactDate] < " & _
Format(DateSerial(iYear, iMonth + 1, 1), "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Graham said:
Looking to use DCount to return a count of specific values - but, what I
appear to be getting is a count of Non Null values.

When I hard code the criteria such as "[CommLearnID] = 8" then the result
is
correct - I have spent hours on this - so frustrating.

Code follows

Option Base 1
Option Compare Database
Option Explicit
Dim iMonth, iYear As Integer
Dim sDateCriteria, sSQL As String
Dim aiCount(50) As Integer
Dim asField, asTable As String


Private Sub btnGo_Click()
Dim iCount, iCount2, iField, iLastVal As Integer
Dim sCriteria, sField As String

asField = Array("CommLearnID", "EnqID", "RefFromID", "RefToID")
'asTable = Array("tContacts", "tcCommLearn", "tcEnqType", "tcRefList",
"tcRefList")

sDateCriteria = "[ContactDate] >= #" & DateSerial(iYear, iMonth, 1) &
"#
And [ContactDate] < #" _
& DateSerial(iYear, iMonth + 1, 1) & "#"
sSQL = "DELETE tContactStats.* " _
& "FROM tContactStats " _
& "WHERE (tContactStats.CalMth = " & iMonth & " And
tContactStats.CalYr = " & iYear & ");"
'MsgBox iMonth & " | " & iYear
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

For iField = 1 To 4
iLastVal = 0
sField = "[" & asField(iField) & "]"
For iCount = 1 To 50
aiCount(iCount) = 0
Next iCount

For iCount = 1 To 50
'sCriteria = "'" & sDateCriteria & " And " & sField & " = " &
iCount & "'"
sCriteria = """" & sField & " = " & iCount & """"

aiCount(iCount) = DCount("[ContactID]", "tContacts", sCriteria)
'MsgBox sCriteria & " | " & iCount & " | " & aiCount(iCount)
If aiCount(iCount) > 0 _
Then
iLastVal = iCount
End If
Next iCount
If iLastVal > 0 _
Then
For iCount2 = 1 To iLastVal
If aiCount(iCount2) > 0 _
Then
sSQL = "INSERT INTO tContactStats (Identifier, ID,
CalYr, CalMth, Count) " _
& "VALUES ('" & asField(iField) & "', " &
iCount2 & ", " & iYear & ", " & iMonth & ", " & aiCount(iCount2) & ");"
'MsgBox sSQL
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

End If
Next iCount2
End If
Next iField
End Sub

Thanks in advance. This forum is invaluable, particularlt for a casual
Access developer.
(Access 2002)
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Oh, sorry. Yes, DCount("[ContactID]", ...) will only count those records
that meet the criteria AND where ContractID is not null.

If you want a count of all rows that match the criteria, whether or not
ContractID is null, use DCount("*", ...)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Graham said:
Thanks Doug

I wiil file that away. But at this time, the date part appears to be
working
fine on the small set of test data.

The issue is with the dynamic creation of the criteria for DCount - I did
originally include all of the code for completeness.

The following gives the expected result
aiCount(iCount) = DCount("[ContactID]", "tContacts", "[CommLearnID] = 8")

But when I build the criteria dynamically is when I seem to get a count of
non Null values - folling is pertinent extracts from original post
asField = Array("CommLearnID", "EnqID", "RefFromID", "RefToID")
sField = "[" & asField(iField) & "]" - the first iteration returns
CommLearnID
For iCount = 1 To 10
sCriteria = """" & sField & " = " & iCount & """"
aiCount(iCount) = DCount("[ContactID]", "tContacts", sCriteria)
MsgBox sCriteria & " | " & iCount & " | " & aiCount(iCount)
Next iCount
Thanks
--
Graham


Douglas J. Steele said:
What is your computer's Short Date format set to in Regional Settings?

If it's dd/mm/yyyy, try:

sDateCriteria = "[ContactDate] >= " & _
Format(DateSerial(iYear, iMonth, 1), "\#mm\/dd\/yyyy\#") & _
" And [ContactDate] < " & _
Format(DateSerial(iYear, iMonth + 1, 1), "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Graham said:
Looking to use DCount to return a count of specific values - but, what
I
appear to be getting is a count of Non Null values.

When I hard code the criteria such as "[CommLearnID] = 8" then the
result
is
correct - I have spent hours on this - so frustrating.

Code follows

Option Base 1
Option Compare Database
Option Explicit
Dim iMonth, iYear As Integer
Dim sDateCriteria, sSQL As String
Dim aiCount(50) As Integer
Dim asField, asTable As String


Private Sub btnGo_Click()
Dim iCount, iCount2, iField, iLastVal As Integer
Dim sCriteria, sField As String

asField = Array("CommLearnID", "EnqID", "RefFromID", "RefToID")
'asTable = Array("tContacts", "tcCommLearn", "tcEnqType",
"tcRefList",
"tcRefList")

sDateCriteria = "[ContactDate] >= #" & DateSerial(iYear, iMonth, 1)
&
"#
And [ContactDate] < #" _
& DateSerial(iYear, iMonth + 1, 1) & "#"
sSQL = "DELETE tContactStats.* " _
& "FROM tContactStats " _
& "WHERE (tContactStats.CalMth = " & iMonth & " And
tContactStats.CalYr = " & iYear & ");"
'MsgBox iMonth & " | " & iYear
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

For iField = 1 To 4
iLastVal = 0
sField = "[" & asField(iField) & "]"
For iCount = 1 To 50
aiCount(iCount) = 0
Next iCount

For iCount = 1 To 50
'sCriteria = "'" & sDateCriteria & " And " & sField & " = "
&
iCount & "'"
sCriteria = """" & sField & " = " & iCount & """"

aiCount(iCount) = DCount("[ContactID]", "tContacts",
sCriteria)
'MsgBox sCriteria & " | " & iCount & " | " & aiCount(iCount)
If aiCount(iCount) > 0 _
Then
iLastVal = iCount
End If
Next iCount
If iLastVal > 0 _
Then
For iCount2 = 1 To iLastVal
If aiCount(iCount2) > 0 _
Then
sSQL = "INSERT INTO tContactStats (Identifier, ID,
CalYr, CalMth, Count) " _
& "VALUES ('" & asField(iField) & "', " &
iCount2 & ", " & iYear & ", " & iMonth & ", " & aiCount(iCount2) & ");"
'MsgBox sSQL
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

End If
Next iCount2
End If
Next iField
End Sub

Thanks in advance. This forum is invaluable, particularlt for a casual
Access developer.
(Access 2002)
Click to expand...
Click to expand...
Click to expand...
 
G

Graham

Thanks again
Problem solved - I had outsmarted myself with quotes.

Changed sCriteria = """" & sField & " = " & iCount & """"
to sCriteria = sField & " = " & iCount
and all looks fine.
--
Graham


Douglas J. Steele said:
Oh, sorry. Yes, DCount("[ContactID]", ...) will only count those records
that meet the criteria AND where ContractID is not null.

If you want a count of all rows that match the criteria, whether or not
ContractID is null, use DCount("*", ...)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Graham said:
Thanks Doug

I wiil file that away. But at this time, the date part appears to be
working
fine on the small set of test data.

The issue is with the dynamic creation of the criteria for DCount - I did
originally include all of the code for completeness.

The following gives the expected result
aiCount(iCount) = DCount("[ContactID]", "tContacts", "[CommLearnID] = 8")

But when I build the criteria dynamically is when I seem to get a count of
non Null values - folling is pertinent extracts from original post
asField = Array("CommLearnID", "EnqID", "RefFromID", "RefToID")
sField = "[" & asField(iField) & "]" - the first iteration returns
CommLearnID
For iCount = 1 To 10
sCriteria = """" & sField & " = " & iCount & """"
aiCount(iCount) = DCount("[ContactID]", "tContacts", sCriteria)
MsgBox sCriteria & " | " & iCount & " | " & aiCount(iCount)
Next iCount
Thanks
--
Graham


Douglas J. Steele said:
What is your computer's Short Date format set to in Regional Settings?

If it's dd/mm/yyyy, try:

sDateCriteria = "[ContactDate] >= " & _
Format(DateSerial(iYear, iMonth, 1), "\#mm\/dd\/yyyy\#") & _
" And [ContactDate] < " & _
Format(DateSerial(iYear, iMonth + 1, 1), "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Looking to use DCount to return a count of specific values - but, what
I
appear to be getting is a count of Non Null values.

When I hard code the criteria such as "[CommLearnID] = 8" then the
result
is
correct - I have spent hours on this - so frustrating.

Code follows

Option Base 1
Option Compare Database
Option Explicit
Dim iMonth, iYear As Integer
Dim sDateCriteria, sSQL As String
Dim aiCount(50) As Integer
Dim asField, asTable As String


Private Sub btnGo_Click()
Dim iCount, iCount2, iField, iLastVal As Integer
Dim sCriteria, sField As String

asField = Array("CommLearnID", "EnqID", "RefFromID", "RefToID")
'asTable = Array("tContacts", "tcCommLearn", "tcEnqType",
"tcRefList",
"tcRefList")

sDateCriteria = "[ContactDate] >= #" & DateSerial(iYear, iMonth, 1)
&
"#
And [ContactDate] < #" _
& DateSerial(iYear, iMonth + 1, 1) & "#"
sSQL = "DELETE tContactStats.* " _
& "FROM tContactStats " _
& "WHERE (tContactStats.CalMth = " & iMonth & " And
tContactStats.CalYr = " & iYear & ");"
'MsgBox iMonth & " | " & iYear
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

For iField = 1 To 4
iLastVal = 0
sField = "[" & asField(iField) & "]"
For iCount = 1 To 50
aiCount(iCount) = 0
Next iCount

For iCount = 1 To 50
'sCriteria = "'" & sDateCriteria & " And " & sField & " = "
&
iCount & "'"
sCriteria = """" & sField & " = " & iCount & """"

aiCount(iCount) = DCount("[ContactID]", "tContacts",
sCriteria)
'MsgBox sCriteria & " | " & iCount & " | " & aiCount(iCount)
If aiCount(iCount) > 0 _
Then
iLastVal = iCount
End If
Next iCount
If iLastVal > 0 _
Then
For iCount2 = 1 To iLastVal
If aiCount(iCount2) > 0 _
Then
sSQL = "INSERT INTO tContactStats (Identifier, ID,
CalYr, CalMth, Count) " _
& "VALUES ('" & asField(iField) & "', " &
iCount2 & ", " & iYear & ", " & iMonth & ", " & aiCount(iCount2) & ");"
'MsgBox sSQL
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

End If
Next iCount2
End If
Next iField
End Sub

Thanks in advance. This forum is invaluable, particularlt for a casual
Access developer.
(Access 2002)
Click to expand...
Click to expand...
Click to expand...
 
Top