Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Criteria for retrieving cases on a monthly basis

Discussion in 'Information Technology' started by JamieM, Jul 28, 2006.

  1. JamieM

    JamieM
    Expand Collapse
    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?
     
  2. Loading...

    Similar Threads Forum Date
    Criteria: When She Deserves to be Raped Hard Talk Dec 21, 2012
    Sikh News Punjab mulling change in pass criteria in board exams (Outlook India) Breaking News Dec 3, 2007

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    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.

    "JamieM" wrote:

    > 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?
     
  4. hippomedon@googlemail.com

    hippomedon@googlemail.com
    Expand Collapse
    Guest

    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
     
  5. JamieM

    JamieM
    Expand Collapse
    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.

    "hippomedon@googlemail.com" wrote:

    > 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
    >
    >
     
  6. hippomedon@googlemail.com

    hippomedon@googlemail.com
    Expand Collapse
    Guest

    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
     
  7. JamieM

    JamieM
    Expand Collapse
    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@googlemail.com" wrote:

    > 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
    >
    >
     
  8. hippomedon@googlemail.com

    hippomedon@googlemail.com
    Expand Collapse
    Guest

    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
     
  9. JamieM

    JamieM
    Expand Collapse
    Guest

    yes, i do.

    Jamie

    "hippomedon@googlemail.com" wrote:

    > 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
    >
    >
     
  10. hippomedon@googlemail.com

    hippomedon@googlemail.com
    Expand Collapse
    Guest

    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
     
  11. JamieM

    JamieM
    Expand Collapse
    Guest

    Thanks for all your help.


    "hippomedon@googlemail.com" wrote:

    > 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
    >
    >
     

Share This Page