Access Week Commencing Dates (Reports)

[Patonar]

Hi,

I have a query which works out which orders are due to be delivered in
a particular week. I would like to display this by week commencing
which i can do with the following WHERE condition in the date field:

<=(Date()) And >DateAdd('d',-5,(Date()))

Now the customer would like to be able to see the previous six weeks
data on the same report - i know i can find out the previous weeks
data by doing something like:

<=(Date()-7) And >DateAdd('d',-5,(Date()-7))

and so on minusing 7 days off for each week in the past - but doing it
this way would mean that i need to have 6 (or however many weeks i
wanted to go back) queries..

Is there anyway that i can dynamically set the where clause when the
report opens? I would be using subreports to display each queries
results...

Regards,

Andy

[F6GGR]

Re: Access Week Commencing Dates (Reports)

You could replace the "-7" by a parameter like [Indicate the nb of days back
wanted] . This question wil be asked (answer : 0,7,14, etc.) when the query
or the report based on this query will be open.

Or make a form with a textbox to indicate the number of days back wanted.
Then mention this textbox in the query instead of "7". ( Something like
[forms]![name of this form]![name of the text box]). The form has to remain
open.

Hope it may help you,

Alan


"Patonar" <patonar@aston. ac.uka écrit dans le message de news:
1162203668.9713 25.293980@h48g2 00...legr oups.com...

Hi,
>
I have a query which works out which orders are due to be delivered in
a particular week. I would like to display this by week commencing
which i can do with the following WHERE condition in the date field:
>
<=(Date()) And >DateAdd('d',-5,(Date()))
>
Now the customer would like to be able to see the previous six weeks
data on the same report - i know i can find out the previous weeks
data by doing something like:
>
<=(Date()-7) And >DateAdd('d',-5,(Date()-7))
>
and so on minusing 7 days off for each week in the past - but doing it
this way would mean that i need to have 6 (or however many weeks i
wanted to go back) queries..
>
Is there anyway that i can dynamically set the where clause when the
report opens? I would be using subreports to display each queries
results...
>
Regards,
>
Andy
>

[Patonar]

Re: Access Week Commencing Dates (Reports)

The only problem with this is i need to do this query six times on the
one report:

For example if the customer wants information from the last 6 weeks
they want a weekly summary in the form:

week commencing dd/mm
---
Order details


So if the user entered 6 weeks as far as they wanted to go back i would
need to query separatly:

42
36
28
21
14
7
0

To show them the summary they require...

How would i do this?

Cheers


F6GGR wrote:

You could replace the "-7" by a parameter like [Indicate the nb of days back
wanted] . This question wil be asked (answer : 0,7,14, etc.) when the query
or the report based on this query will be open.
>
Or make a form with a textbox to indicate the number of days back wanted.
Then mention this textbox in the query instead of "7". ( Something like
[forms]![name of this form]![name of the text box]). The form has to remain
open.
>
Hope it may help you,
>
Alan
>
>
"Patonar" <patonar@aston. ac.uka écrit dans le message de news:
1162203668.9713 25.293980@h48g2 00...legr oups.com...

Hi,

I have a query which works out which orders are due to be delivered in
a particular week. I would like to display this by week commencing
which i can do with the following WHERE condition in the date field:

<=(Date()) And >DateAdd('d',-5,(Date()))

Now the customer would like to be able to see the previous six weeks
data on the same report - i know i can find out the previous weeks
data by doing something like:

<=(Date()-7) And >DateAdd('d',-5,(Date()-7))

and so on minusing 7 days off for each week in the past - but doing it
this way would mean that i need to have 6 (or however many weeks i
wanted to go back) queries..

Is there anyway that i can dynamically set the where clause when the
report opens? I would be using subreports to display each queries
results...

Regards,

Andy

[Lyle Fairfield]

Re: Access Week Commencing Dates (Reports)

Patonar wrote:

So if the user entered 6 weeks as far as they wanted to go back i would
need to query separatly:

Assuming that your query can return all the records required, perhaps
you could group the report by week of the order date.

The Where condition would be something like:

WHERE OrderDate

>= DateAdd("ww", -6, Date())

AND
OrderDate
< DateAdd('ww', 1, Date())

To group by week choose a date field on which to group and in the
GroupProperties Dialog click on Group On and choose from the drop-down.

Through the use of Report Grouping you could direct that each each week
begin on a new page and have an appropriate heading.