Criteria for retrieving cases on a monthly basis

[Guest]

I need to retrieve records on a monthly basis that need to be reviewed 2
months from the Received Date. I have a field named "2 months Review:
[RECEIVED DATE]+60" which shows the day(mm/dd/yyy) it needs to be reviewed.

So I would like to enter criteria and/or a parameter, which will prompt me
(or another user) to enter a month (for example: May) and it will retrieve
those records for the month of May and/or any records that are "past due"
their Review Date (for example: cases that needed a 2 month review by the end
of April or March, etc.) How would I do this?

 

[Guest]

any records that are "past due" their Review Date
Use your field named "2 months Review: [RECEIVED DATE]
and criteria <=Date()-60

If you received an item 60 days ago or more it will show as overdue.

 

[hippomedon]

Hi Jamie,

Karl's suggestion is the simplest way to catch overdue reviews.

If you really want to give the user the choice of selecting the
timeframe, you should probably consider building a dynamic report
(you'll need to know/ learn a little VBA).

Basically, you build a report linked to your query. When you open the
report, you use VBA code to open a dialog form which stops report from
opening further until certain options are selected. The dialog form
gives the option of selecting date From values. This value is stored in
the textbox DateFrom. These values filter the query in the criteria
section for the [Date Received] field via the following:

=[Forms]![DialogFormName]![DateFrom]. Then the user presses a command button on the dialog called "Go" which hides the form. This allows the VBA code in the report to continue. The report then calls the query which uses the value from the dialog form to restrict the records shown according to the date selected.

Martin Green provides a very nice tutorial for some inspiration.
http://www.fontstuff.com/access/acctut19.htm

Alternatively in the criteria for the received date, you could enter

=[DateFrom] in brackets as shown here. When the query is opened the user will be prompted for a value for [DateFrom]. This is a very simple way of allowing user to filter a query. Unfortunately, it is somewhat user unfriendly (since you cannot give instructions on the popup) and will require "training".

Hope this helps!
Paul

 

[Guest]

OK...I tried the <=[DateFrom] in the 2 months Review field. When prompted, I
entered 5/31/06 and it worked...in a way. It retrieved 36 records; however,
the total retrieved should be 43 records. Also, the 7 missing records are
not at the beginning or end but in the middle with review dates ranging from
5/6/06 to 5/9/06. do you have any explanation why this occurred?

Also, thanks for the link to the tutorial re dynamic reports. I will try it
but I thought the above criteria would work for now until i figured out the
dynamic reports.

Hi Jamie,

Karl's suggestion is the simplest way to catch overdue reviews.

If you really want to give the user the choice of selecting the
timeframe, you should probably consider building a dynamic report
(you'll need to know/ learn a little VBA).

Basically, you build a report linked to your query. When you open the
report, you use VBA code to open a dialog form which stops report from
opening further until certain options are selected. The dialog form
gives the option of selecting date From values. This value is stored in
the textbox DateFrom. These values filter the query in the criteria
section for the [Date Received] field via the following:

=[Forms]![DialogFormName]![DateFrom]. Then the user presses a command button on the dialog called "Go" which hides the form. This allows the VBA code in the report to continue. The report then calls the query which uses the value from the dialog form to restrict the records shown according to the date selected.

Martin Green provides a very nice tutorial for some inspiration.
http://www.fontstuff.com/access/acctut19.htm

Alternatively in the criteria for the received date, you could enter

=[DateFrom] in brackets as shown here. When the query is opened the user will be prompted for a value for [DateFrom]. This is a very simple way of allowing user to filter a query. Unfortunately, it is somewhat user unfriendly (since you cannot give instructions on the popup) and will require "training".

Hope this helps!
Paul

 

[hippomedon]

Hi Jamie,

Is this the only criteria you are currently using? Or is there
something else that could cause those records not to be displayed?

Paul

 

[Guest]

Paul

the other criteria in the query is as follows:

Audit: 0
Ltr. Written: Is Null
Final Action: Is Null

however, when looking at the main table, it shows nothing in these fields so
they should be included in the query.

Jamie

 

[hippomedon]

Jamie,

When you run the query without the <=[DateFrom] criteria and then
filter the [ReviewField] with <=#05/31/06#, do you get all 43 expected
records?

Paul

 

[Guest]

yes, i do.

Jamie

 

[hippomedon]

Jamie,

I've never encountered such a problem with this method before. I'm
sorry! Perhaps someone else reading this post may have an idea.

Sorry,
Take care,
Paul

 

[Guest]

Thanks for all your help.