[AccessD] custom sorts/groups in a report

Egads... :)

All  I want is to group/sort a report based on the week -- but the first day
of the week needs to be dynamic. The user chooses the first day of the week
and the report sorts/groups accordingly. For instance, if the user chooses
Wednesday, each group begins with a Wed and ends with a Tuesday. 

Right now, the process begins with the simple "pick a day" form, which runs
the report -- that's based on a query that calls a function to return the
"ww" component for each date in the query, so the report can then group on
that value -- in the report that Jack built. 

Seems like a long way around to go to me. It does work and it isn't horribly
slow or anything. It just seems inefficient to me that I can't get all that
done in the report or the query without the aid of calling that function.
That's where the openargs comes in -- I've tried passing the
firstdayoftheweek value to the report and calculating the "ww" components in
the report, but I can't get the report to then sort on those "ww" values
because of the sorting limitations. 

I just don't believe that the solution I've come up with is the most
efficient, even if it does work. 

Susan H. 

Sorry, Susan, but I just tuned in. <g>  

I'm not clear on what you're doing so I'm not being much help.  I do dynamic
reports for a week period and pass the start date to my report then let the
report determine what day that is and populate an appropriate series of
label captions to match.  I base it on a crosstab query, rebuilt for each
report run, that uses dates as column headings and uses parameters to filter
the dates.  I set the SQL for that crosstab query each time I use a form to
call the report (no hand tweaking), and I generate an IN list of dates in
the specified range to create the column headings.  I then use code in the
Report_Open event to determine which field to bind to which control in the
detail of the
report.  Is that kind of what you're attempting to do?   

Charlotte Foust

-----Original Message-----
From: Susan Harkins [mailto:ssharkins at bellsouth.net]
Sent: Monday, April 19, 2004 11:52 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] custom sorts/groups in a report


 
You can only group and sort on a field or an expression involving a
field, and no, you can't use variables.  Had you mentioned the variable
before? Why not just create a "week" field in the query and group and
sort on that calculated field.  Then you could use your variable to
create a filter for the particular week in question.

============I'm passing the firstdayoftheweek value from a form to the
report -- the report's based on a query. So that's the quandry. That's
what I mentioned in my last message -- the only way I can see around it
is to pass the value to the query and run the expressions in the query
first. I'm not sure I know how to do that really -- I have never passed
anything to a query before -- just called functions from a query. And in
this case, that's what I'm trying to avoid -- calling the function from
the query. 

Susan H. 

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com