Reports due date

[Ldydi525]

Hello
I created a database to generate grants received. I would like to know how
to tracks with reports are due. The reports due dates varies per item such
as, bimonthly, monthly, annually, no reports. Please help

 

[Smartin]

Hello
I created a database to generate grants received. I would like to know how
to tracks with reports are due. The reports due dates varies per item such
as, bimonthly, monthly, annually, no reports. Please help

Hello,

It's hard to give a specific answer since we do not know how you have
your table(s) set up. A couple approaches to consider:

1) Add a field to the main table wherein you input the number of days
within which a report is due. E.g., bimonthly = 60, monthly = 30, annual
= 365, no report = 0. This solution will lead to approximate due dates
(e.g., because not all months have 30 days), but might be close enough.

2) Create a helper table with discrete options for time frames, such as
bimonthly, monthly, etc. Create a field in the main table that requires
a value from the helper table e.g. using a combo box (you are using a
form to do data entry, yes?) and establishing a relationship between the
main and helper tables.

In either case you will need to write a query to determine the next due
date for a report based on the due date rule you implement. The results
from this query can populate an unbound field on a form, create overdue
reports, etc. The exact method rather depends on your table structure,
and the "no report" exception will require some special handling, but
this these should not be too difficult.

Hope this gives you some ideas.

 

[Ldydi525]

Thank you the suggestion, but I need futher assitance. I created a field
with 180 to determine 6 months, 90 to determine quarterly reports, 0 for
null, 360 for annual report. Then, I created a calculated field in the
query, [Exp] * [Date] I used today date, to use as an example and my results
was _7/04/3040 due date for the 180 amount. Please help, I am using 2/14/07
as the date and 180 for 6months.

Also, how can I setup the database to generate a due date from the last
report.


Thanks

 

[Smartin]

Try something like this in your calculated field:

DateAdd("d",180,Date())

This will return 180 days from today.

To base the due date from a previous report you will need to know how to
find the date in question from that report. Substitute "Date()" in the
expression above with the report date. A function such as Max or DMax
might be very useful here. Check Help for how they work.

Hope this helps!

Thank you the suggestion, but I need futher assitance. I created a field
with 180 to determine 6 months, 90 to determine quarterly reports, 0 for
null, 360 for annual report. Then, I created a calculated field in the
query, [Exp] * [Date] I used today date, to use as an example and my results
was _7/04/3040 due date for the 180 amount. Please help, I am using 2/14/07
as the date and 180 for 6months.

Also, how can I setup the database to generate a due date from the last
report.


Thanks

Hello,

It's hard to give a specific answer since we do not know how you have
your table(s) set up. A couple approaches to consider:

1) Add a field to the main table wherein you input the number of days
within which a report is due. E.g., bimonthly = 60, monthly = 30, annual
= 365, no report = 0. This solution will lead to approximate due dates
(e.g., because not all months have 30 days), but might be close enough.

2) Create a helper table with discrete options for time frames, such as
bimonthly, monthly, etc. Create a field in the main table that requires
a value from the helper table e.g. using a combo box (you are using a
form to do data entry, yes?) and establishing a relationship between the
main and helper tables.

In either case you will need to write a query to determine the next due
date for a report based on the due date rule you implement. The results
from this query can populate an unbound field on a form, create overdue
reports, etc. The exact method rather depends on your table structure,
and the "no report" exception will require some special handling, but
this these should not be too difficult.

Hope this gives you some ideas.