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 3 3284
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************* *********@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
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************* *********@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 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: androtech |
last post by:
Hello,
I'm looking for a function that returns a date range for a specified week
number of the year.
I'm not able to find functions like this anywhere. Any pointers/help would
be much appreciated.
TIA
|
by: David Olsen |
last post by:
Would any of you kind souls now how to put into an unbound text box
the week commencing date for the current week which you are in, so
that the report heading would say something like for example, orders
registered to date this "week commencing"--then the text box.Using
Monday as the first day of the week.
Many Thanks
David
|
by: Cillies |
last post by:
Does anyknow how to control dates in acess 2000, the thing is I want
to search for records using a form by date. I will be using a between
date search box. But i want to enter say todays date but retrieve the
results for exactly one month later.
E.G.
I if I enter 10-05-04 to 14-05-04 I want to see the records for
exactly 30/31 days later.
I was thinking could I manipulate the "Today'sDate" function + 30days.
|
by: neoteny2 |
last post by:
I need MS Access to automatically create reports/subreports based on
specific criteria. I am building a database in Access 2003 with
different locations/sites. I have the "sites" table created containing
site info, including an empty field for "date". I also have a report
format already created displaying the site info. I need Access to ask
the user for a date, calculate three additional dates using the entered
date (adding or subtracting...
|
by: planetthoughtful |
last post by:
Hi All,
I'm building some reports in Acc97 and using a custom calendar form to
allow users to pick dates with which to report.
I'm wondering if there's an easy way in code to be able to
automatically work out the end-of-week date and end-of-month date from
an arbitrary date value?
To explain a little further. Let's say a user wants to view a weekly
| |
by: aarklon |
last post by:
Hi folks,
I found an algorithm for calculating the day of the week here:-
http://www.faqs.org/faqs/calendars/faq/part1/index.html
in the section titled 2.5 what day of the week was 2 august 1953
I checked the algorithm it is very correct.
|
by: Patonar |
last post by:
Hi,
I have a database that lists customer orders. There is a field within
this customer order table that shows DeliveryDate - these could be
dates any day during the working week.
I need to be able to develop a report that Sums the total QTY ordered
by Week Commencing - how would i go about doing this?
Presumably i need somehow to calculate when the week commencing is in
|
by: sbaird |
last post by:
Aloha from Hawaii,
I'm beating my head on the wall here. I have a recruiting contact
managment database I'm trying to create. Managers (there ar 14 of
them) have to make a certain number of recruiting calls per week, and
so many of them have to result in at least an appointment. However,
the "week" is Wednesday, 12:01pm to the following Wednesday, 12:00noon.
So here are my questions...
1) Is there a way to change the standard week...
|
by: dpotter7 |
last post by:
I'm new to Access and I have Access 2003. I'm creating a database to manage projects that our company puts out bids for and I need to find a way to be able to track the status of them weekly. I have dates of when bids are made and when they are signed by the client, but I want to have the database be able to keep track of every week which ones were pending during which week. Basically, I want to try to get the database to recall that...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |