DCount

  • Thread starter Thread starter t.y.
  • Start date Start date
T

t.y.

In my report, I'm using the following formula to count
records: =DCount("[Case
ID+]","sqryAllDataRepList","[Arrival Time] Between #" &
[Forms]![frmSLAReportDialogBox]![BeginningDate] & "# and
#" & [Forms]![frmSLAReportDialogBox]![EndingDate] & "#")
It works fine. But in addition to [Arrival Time], I also
need to filter on [RepLocation]. And I can't get that to
work. For example, I need to count records where
[Arrival Time] is between may and june and where
[RepLocation]=Boston.
 
In my report, I'm using the following formula to count
records: =DCount("[Case
ID+]","sqryAllDataRepList","[Arrival Time] Between #" &
[Forms]![frmSLAReportDialogBox]![BeginningDate] & "# and
#" & [Forms]![frmSLAReportDialogBox]![EndingDate] & "#")
It works fine. But in addition to [Arrival Time], I also
need to filter on [RepLocation]. And I can't get that to
work. For example, I need to count records where
[Arrival Time] is between may and june and where
[RepLocation]=Boston.
Click to expand...

How are you selecting 'Boston'?
From a Combo Box drop-down or writing it in as text?
Does this help?

=DCount("*","sqryAllDataRepList","[Arrival Time] Between #" &
[Forms]![frmSLAReportDialogBox]![BeginningDate] & "# and
#" & [Forms]![frmSLAReportDialogBox]![EndingDate] & "# AND
[RepLocation] = '" & forms!frmSLAReportDialogBox!ControlName & "'")

The above assumes the [RepLocation] is stored as text, not a Number,
and that the Control ([ControlName]) on the form is also a text
control or a Combo Box bound to a Text column, not a number.

If [RepLocation] is actually a Number datatype, and the control on the
form is a combo box bound to a Number coulumn, use, as the second
critieria:
..... "# AND [RepLocation] = " &
forms!frmSLAReportDialogBox!ControlName)
 
TY,

Try it like this...
=DCount("*","sqryAllDataRepList","[Arrival Time] Between
[Forms]![frmSLAReportDialogBox]![BeginningDate] And
[Forms]![frmSLAReportDialogBox]![EndingDate] And [RepLocation] =
[Forms]![frmSLAReportDialogBox]![Location]")
 
You may be missing something quite simple. Is your report's record source
the same records as "sqryAllDataRepList"? And, are these records already
limited to the dates from the text boxes? If so, you can trash the DCount()
in favor of:
=Sum( Abs(Not IsNull([CaseID+]) And [RepLocation] = "Boston") )

If CaseID+ is never null then remove "Not IsNull([CaseID+]) And "
 
Back
Top