Getting Access to auto enter dates in a report

[Jay/UK]

I have an Access 97 database which is perfect for my company's needs in
every respect - except one !!

I need my database reports to automatically insert - perhaps just before
printing them? - dates which occur in the future according to a pre-defined
"timetable", for example, I'd like it to insert a date and have it
automatically "knock it forward" to 4 weeks from now when that date is past.

I am sure there MUST be a way of getting Access - as it knows the current
date (I use the automatic field for entering the current date) - to simply
jump forward a pre-set number of days and print out THAT date.

Or even, say, on a Wednesday to tell it to jump forward to the following Monday's
date (say now It'd be 31st July).

I will be more than happy to elaborate on the database design, but I am
hopeful that by putting my enquiry briefly, one of you kind people will be
able to point me in the right direction?

Thank you in anticipation.

Kind Regards,
Jay/UK

 

[Rich@ITTC]

Hi Jay

I am right in thinking you want to display on your report the date four weeks from now? If the value you wish for this furure date is always consistant (i.e. always four weeks) then create an unbound text box on your report and set the Control Source to

=Date()+28

That should do the trick!

[This message has been edited by Rich@ITTC (edited 07-27-2000).]

 

[Jay/UK]

Dear Rich,

Thank you so much for your response.

I'm not too clear on two points:

"do I enter the starting date between teh brackets"

"in what format do I enter the starting date"

I need to give each report a starting date, eg. next week's ones would be
Monday 31st July 2000, Tuesday 1st August 2000 ... and so on - and have it
"knock forward" 28 days each time the report is produced.

Much obliged for your assistance.

Kind Regards,
Jay/UK

 

[Rich@ITTC]

Hi Jay

The expression I suggested assumes you wish to use TODAY'S date and then add four weeks on from that. Access has various functions that work off the computer's settings (in this case off the internal clock). See below:

Time() - this will automatically give you the current time (according to your computer clock)

Date() - this will give you the date

Now() - this will give you the time and the date.

You do not enter anything into the brackets - the whole expression is put into the Control Source of your unbound Text Box e.g.

Control Source: =Date()+28


If you wish to be able to specify the start date for the calculation (eg. today is Sunday 30/07/00, but you want to run a report with the date being Tuesday 01/08/00 plus 28 days) then you will need to remove Date() and replace it with a stored field such as [ReportDate]. This field, ReportDate could be in yourquery/table that the report is based on or perhaps it could be on your form just above the command button to preview the report. The report would then reference this value for the expression within the date text box. So on your report you would have an unbound text box with the Control Source set to:

=Forms!frmMyReport![reportdate]+28

This may answer your question, though I am not sure it will as I do not fully understand why you require a report to be dated four weeks in the future.

Hope it helps.

 

[Jay/UK]

Dear everyone who responded,

Maybe some of you very kind people who responded to my enquiry may be able to help me further if I elaborate on the purpose and layout of my database.

So this is what I will do:

~~~

I have a number of reports set up which act as "templates" for the work
schedules that the field staff use (21 of them), day in-day out. They clean
machines for my company's clients, some clients require daily cleaning,
others a few times a week, some weekly - and most significantly some require
cleaning services every 2 weeks or every 4 weeks.

In order to achieve the above, and especially the last 2 requirements, there
are fields in the database for each day in 4 weeks ... Week 1/Monday, Week
1/Tuesday and so on ... until Week 4/Friday. Each field is a "Yes/No"
field. Daily's have a "Yes" in all fields, 4 weeklies would have a "Yes" in
just one of the fields.

So when I want to print one weeks' schedule's, I run the report for Week 1 (Monday, Tuesday ...) - they're attached to queries which pick out the relevant records) and when I want to do Week 2 likewise and so on until I come around to Week 1 again.

The reports are produced - generally - the Wednesday before the week the reports cover.

My point of concern is that the work date has to be on each and every report
I print - and at the moment I manually enter it into each report, obviously
doing all of Monday's first (eg. Monday 31st July) so I can just do a copy
and paste for each report.

But this is very, very time consuming - and not practical when (like very recently) I am off work, "sick as a dog" and a colleague, also very busy, has to try and cover this weekly job.

~~~~

Can you assist further? knowing the background of the purpose of my
database, Week 1 represents say w/c 31st July, Week 2 represents w/c 7th
August, Week 3 represents w/c 14th August, Week 4 represents w/c 21st August
.... with the next Week 1 being for w/c 28th August and so on .... ad
infinitum !!

I am extremely grateful for your patient support and guidance, which is
invaluable.

Kind Regards,
Jay/UK