Grouping by month

[Guest]

I have a report which is based on a query, say Qry2. Qry2 is based on another
query say Qry1. There is a calculated field to calculate Year and Month in
Qry2 as YM:Year([submit date]) & Format(Month([submit date]),"00") where
[submit date] is a field in Qry1. I am using this field to sort my records
only and not showing it in Qry2 because I dont want to show it in the query
results. I am trying to group my records in the report by this field. Since I
am not showing YM in Qry2, it does not appear in the field list in the
report. Hence I tried using the expression =Year([submit date]) &
Format(Month([submit date]),"00") directly as a grouping level in the Sorting
and Grouping window of the report. When I try to run the report , it doesn't
recognize the field, [submit date] and asks its parameter value. Does this
mean that the report cant recognize a field from a query (Qry1) which is not
directly underlying it even though Qry1 is based on a query underlying the
report (Qry2). How can I make the report recognize [submit date] . (If I
start showing the field YM in Qry2, then, I can group records by YM in report
but I dont want to show it in Qry2)

 

[Jeff Boyce]

The sort order you establish in your query is NOT how Access sorts reports.
You'll need to include the "sorting fields" from your query to set the sort
order in your report definition. Look for "Sorting and Grouping" in the
report definition.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Duane Hookom]

Your report's sorting and grouping can use fields/expressions from the
record source of your report. If the field isn't in the record source, it
can't be used.

I don't understand why you would not include the Qry2.