Every 30 days

[Mark]

Hello alll again,
I need a report that will print clients that need to be seen every 30 days
from their date of entry, on a daily basis. I have all the fields necessary
(i believe) but am uncertain of how to write the expression. My client table
has a DOE (date of entry) field, client name and of course a unique
identifier. It would be nice to have a pop up at the main data input screen
to notify users to print the report, but thats just on my wish list. Thanks

 

[Allen Browne]

Create a table with a Number field, and enter records from 1 to (say) 1000.
(I will assume this table is named tblCount, and the Number field is named
CountID.)

Create a query that uses your existing table plus the counter table. There
must be no join between these tables in the upper pane of table design.

Assuming your main table has fields named:
DOE Date/Time date of entry
Revisit Number number of days apart (e.g. 30.)
type an expression like this into a fresh column in the Field row:
Due: DateAdd("d", tblCount.CountID * [Revisit], [DOE])

The unjoined tables give you every possible combination, so for every record
in your main table, you get 1 period after, 2 periods after, etc. This gives
you each date from the start onwards. (You can add criteria to limit how far
it goes into the future if you wish.

If you want to enter all the numbers into the table programmatically instead
of typing them in, use the code at the end of this article:
http://allenbrowne.com/ser-39.html

 

[Jeff Boyce]

Mark

Every day after 30 days has passed is "30 days (or more) after the DOE".
Won't you need to keep track of whether or not a "30" (and "60", and "90",
and ...) day notice was sent?

Or do you just want to run a report once each month and send to
anyone/everyone more than 30 days "old"?

I'm guessing you'd need to keep track of whether or not the report was
already run (this month), and/or whether the run was successful (and Access
can't help you determine that, since the printer might have choked in mid
run).

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mark]

greetings Jeff,
No I don't need the 30,60.90 funny you mentioned this because I already have
such a report, I don't need to know if it was sucessfully run, in fact I
want any user to be able to run it on demand, keeping in mind that each
client belongs to one staff member. Running it once a month would actually
work though. My database is all forms and buttons for the users, and complete
within the table with about all the field of client info you can imagine.
What else would be useful for you to know to help me?

 

[Jeff Boyce]

Mark

It sounds like you are describing a report that would be run just once each
month. So, if the "1st" ("run this report on the 1st of each month") falls
on a weekend, and no one opens your application, then the next workday, when
someone starts up your app, it will NOT know to run it anyway.

Are you sure you don't need a way to keep track of the last time it ran
(even if that date is manually entered by a user)?!

Depending on your skills/experience in coding, this could turn into a bit of
a project. I tend to look for simple solutions first -- wouldn't it be
easier to add a recurring task in Outlook reminding yourself to run the
report on the first (?last) workday of each month? Otherwise, you will be
needing to replicate some of Outlook's scheduling capabilities in Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP