How to do this.....?

[phil03]

At work I've created a simple reports DB, that emails report to our
various recipients.
I say reports, it is in fact a dynamically built SELECT query, exported
into Excel and sent as an email attachement.
The initial spec was for it to run certain reports on certain days each
and every week.
No problem, created a SELECT CASE structure and put the code into the
appropriate CASE values.

Now my quandary - the spec has changed for certain reports to be sent
fortnightly instead of weekly, and I'm stuck at a method to acheive
this.
On a Monday report A,B & C get sent, the following Monday report D,E &
F get sent, then the following Monday after that A,B & C get sent.

I can't fathom out a method which will allow me to send the reports in
such a cycle.

Ant pointers would be greatly appreciated

Cheers

[Peter Hoyle]

<ph****@globalnet.co.uk> wrote in message

....
Now my quandary - the spec has changed for certain reports to be sent
fortnightly instead of weekly, and I'm stuck at a method to acheive
this.
On a Monday report A,B & C get sent, the following Monday report D,E &
F get sent, then the following Monday after that A,B & C get sent.

I can't fathom out a method which will allow me to send the reports in
such a cycle.

You could set a start date for the new reports
dteStart = #3/7/05#

Then find the difference between this and the current date in weeks:
intDiff = DateDiff("ww", dteStart, date, vbMonday)

Then use the Mod function to find which reports to send
intReportWeek = intDiff Mod 2
If intReportWeek = 1 Then
'send reports A, B, C
Else
'send reports D, E, F
End If

--
peter
http://www.peterhoyle.co.uk

[phil03]

Hi Peter,

Many thanks for the pointers and even some code to boot!
yours is a far elegant solution to what I had been trying!
I'd resigned myself to a lookup table and in hindsight that would of
been far too much administration and effort.

Thanks again, it is greatly appreciated.

Cheers

phil

Peter Hoyle wrote:

<ph****@globalnet.co.uk> wrote in message

....
Now my quandary - the spec has changed for certain reports to be sent fortnightly instead of weekly, and I'm stuck at a method to acheive
this.
On a Monday report A,B & C get sent, the following Monday report D,E & F get sent, then the following Monday after that A,B & C get sent.

I can't fathom out a method which will allow me to send the reports in such a cycle.

You could set a start date for the new reports
dteStart = #3/7/05#

Then find the difference between this and the current date in weeks:
intDiff = DateDiff("ww", dteStart, date, vbMonday)

Then use the Mod function to find which reports to send
intReportWeek = intDiff Mod 2
If intReportWeek = 1 Then
'send reports A, B, C
Else
'send reports D, E, F
End If

--
peter
http://www.peterhoyle.co.uk