Re: Displaying the same data separately on a report

Unless you have a ridculous table structure (such a separate
table for each week), there should be no need to use a union
query. Just set a date range criteria on the date field.
If you don't understand that, post a Copy/Paste of your
current query's SQL view.

Your grouping expression won't sort correctly for weeks 9
and 10 or when the weeks are in different years. Try this
expression instead:

Year(date) & Format(DatePart("ww", date), "00")

How would you like to format the date? There is no need to
display the date in a text box based on what you used to
sort/group, just format the date field. Try looking in VBA
Help for "Format Property" and follow the links to the type
of data you want to format.
--
Marsh
MVP [MS Access]


Susan L wrote:
Thanks. A colleague showed me how to do a union query to join the data from
my Current Week and Next Week queries. And I've created a report that groups
on the week/year generated by Format([DateFieldName],"wwyyyy"). So that part
works.

Now I wonder whether there's a function I could use to reformat the date
produced by the expression above that I'm grouping on, e.g., 52007 and 62007
(presumably for the 5th and 6th weeks of 2007). I tried Help but didn't find
anything.


"Marshall Barton" wrote:

Susan L wrote:

My users want to see scheduled events for the current week and the next week
on the same report -- but to have the weeks separated, e.g., Current Week,
Next Week. I thought this would be simple with a report and subreport, but
the problem is that there is no parent/child relationship between this week's
and next week's data and The report/subreport jumbled the data strangely. I
have two queries that pull the correct data.

Here's what I want it to look like:
Cycle State Event Scheduled Date Time
Current Week
1 Virginia Teleconference 1/30/07 11 am
2 Missouri Shipment 1/31/07 n/a

Next Week
1 Louisiana Teleconference 2/6/07 2 pm
2 Arizona Installation 1/8/07 3 pm

I'm sure I'm just not seeing something. Any help would be appreciated.


If you can use a single query to get all the desired data,
then scrap the subreport and use Sorting and Grouping to
separate the weeks.
.

Relevant Pages

  • Re: step through table and select data for reporting values
    ... I set the dates to "mmdd" vs the output the query is currenly giving me ... I've tried to include the format in the expression itself but this ... I have a report with an unlimited amount or rows. ... > 3) Add the Primary key field and the "Jobs" table foreign key to the grid. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Four Report Problems
    ... I changed to the query like I thought I had, ... I just typed the names of the banks in the Lookup tab. ... Field List into the Report. ... Don't use an expression to format a value. ...
    (microsoft.public.access.reports)
  • Re: OutputTo to Excel question
    ... Excel file and opening up ... clicks Ok and your query is exported) ... If Macro is going to keep on giving the format error problem, ... Save a query that pulls the same data as the report. ...
    (microsoft.public.access.macros)
  • Re: OutputTo to Excel question
    ... replace qryExport with the name of your table or query ... If Macro is going to keep on giving the format error problem, ... However, once I trigger the Macro to OutputTo the Query into Excel, it ... Save a query that pulls the same data as the report. ...
    (microsoft.public.access.macros)
  • Re: OutputTo to Excel question
    ... If Macro is going to keep on giving the format error problem, ... So how do I go about Exporting a Query by VB? ... However, once I trigger the Macro to OutputTo the Query into Excel, it ... Save a query that pulls the same data as the report. ...
    (microsoft.public.access.macros)