Count Records In Code In Group Header

[Melissa]

I have a sales report where the sales are grouped by district. The report is
based on a query with criteria for sales date to get the sales in any
specified date range. I have code in the Format event of the district group
header. How do I get the count of sales in the current district in the code
in the Format event of the district group header?

Thanks!

Melissa

[Wayne Morgan]

Try a DCount statement in a calculated textbox or in code.

Example:
DCount("*", "NameOfReportsRecordSource", "[GroupID]=" & Me.txtGroupID)

If the code is in a calculated textbox, leave out the "Me.". Also, this
assumes the value to be a number, if it is a text value, you'll have to
concatenate in quotes around it also.

Example:
DCount("*", "NameOfReportsRecordSource", "[GroupID]='" & Me.txtGroupID &
"'")
or
DCount("*", "NameOfReportsRecordSource", "[GroupID]=""" & Me.txtGroupID &
"""")

For this to work, you will need to place a textbox in the group header and
bind it to this field. You can set the Visible property of the textbox to No
or False if you don't need it to show for something else.

--
Wayne Morgan
Microsoft Access MVP
"Melissa" <mb****@earthlink.net> wrote in message
news:3B*****************@newsread2.news.atl.earthl ink.net...

I have a sales report where the sales are grouped by district. The report
is
based on a query with criteria for sales date to get the sales in any
specified date range. I have code in the Format event of the district
group
header. How do I get the count of sales in the current district in the
code
in the Format event of the district group header?

Thanks!

Melissa

[Melissa]

Wayne,

Thank you for responding! I used the expression you suggested. My value is
text so I concatenated as you showed. When I run the code to use the DCount
value I get the message "You Entered An Expression That Has No Value". The
code is actually calling a function that uses the DCount expression. It
seemed to me the message was saying txtGroupID (my code uses DistrictID) has
no value when the function is called from the On Format event. Just out of
curiosity I added the following to the code:
MsgBox Reports!SalesReport!MyField

No matter which field I put in the above expression, I keep getting the same
message. So it seems that the SalesReport does not yet have any data when
the On Format event fires. This being the case, you suggestion is not going
to work to count the records. Any thoughts??

Melissa
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:ll******************@newssvr33.news.prodigy.c om...

Try a DCount statement in a calculated textbox or in code.

Example:
DCount("*", "NameOfReportsRecordSource", "[GroupID]=" & Me.txtGroupID)

If the code is in a calculated textbox, leave out the "Me.". Also, this
assumes the value to be a number, if it is a text value, you'll have to
concatenate in quotes around it also.

Example:
DCount("*", "NameOfReportsRecordSource", "[GroupID]='" & Me.txtGroupID &
"'")
or
DCount("*", "NameOfReportsRecordSource", "[GroupID]=""" & Me.txtGroupID &
"""")

For this to work, you will need to place a textbox in the group header and
bind it to this field. You can set the Visible property of the textbox to No or False if you don't need it to show for something else.

--
Wayne Morgan
Microsoft Access MVP
"Melissa" <mb****@earthlink.net> wrote in message
news:3B*****************@newsread2.news.atl.earthl ink.net...

I have a sales report where the sales are grouped by district. The report
is
based on a query with criteria for sales date to get the sales in any
specified date range. I have code in the Format event of the district
group
header. How do I get the count of sales in the current district in the
code
in the Format event of the district group header?

Thanks!

Melissa

[Melissa]

Thanks for your help, Wayne! I got your recommendation to work.

Melissa
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:ll******************@newssvr33.news.prodigy.c om...

Try a DCount statement in a calculated textbox or in code.

Example:
DCount("*", "NameOfReportsRecordSource", "[GroupID]=" & Me.txtGroupID)

If the code is in a calculated textbox, leave out the "Me.". Also, this
assumes the value to be a number, if it is a text value, you'll have to
concatenate in quotes around it also.

Example:
DCount("*", "NameOfReportsRecordSource", "[GroupID]='" & Me.txtGroupID &
"'")
or
DCount("*", "NameOfReportsRecordSource", "[GroupID]=""" & Me.txtGroupID &
"""")

For this to work, you will need to place a textbox in the group header and
bind it to this field. You can set the Visible property of the textbox to No or False if you don't need it to show for something else.

--
Wayne Morgan
Microsoft Access MVP
"Melissa" <mb****@earthlink.net> wrote in message
news:3B*****************@newsread2.news.atl.earthl ink.net...

I have a sales report where the sales are grouped by district. The report
is
based on a query with criteria for sales date to get the sales in any
specified date range. I have code in the Format event of the district
group
header. How do I get the count of sales in the current district in the
code
in the Format event of the district group header?

Thanks!

Melissa