By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,347 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,347 IT Pros & Developers. It's quick & easy.

Access Week Commencing Dates (Reports)

P: n/a
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

Oct 30 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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" <pa*****@aston.ac.uka écrit dans le message de news:
11**********************@h48g2000cwc.googlegroups. 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

Oct 30 '06 #2

P: n/a
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" <pa*****@aston.ac.uka écrit dans le message de news:
11**********************@h48g2000cwc.googlegroups. 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
Oct 30 '06 #3

P: n/a
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.

Oct 30 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.