Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Unanswered: Pulling Records based on user input

    I have a table called followup schedule with 5 fields: here is an example
    RoomID 1 monthfollowup 3 monthfollowup 6 monthfollowup 9month followup
    1234 6/8/2012 8/8/2012 11/8/2012 2/8/2013
    2345 7/27/2012 9/27/2012 12/27/2012 3/27/2013
    5534 8/22/2012 10/22/2012 1/22/2013 4/22/2012

    My question is how can i pull just records that meet user input criteria start date and end date for exemple start date 07/01/2012 end date 08/30/2012 and pulls just records that falls b/w those dates. any help is really appreciated.

  2. #2
    Join Date
    Sep 2011
    Posts
    5
    Assuming your follow up dates aren't literally 1, 3, 6, and 9 months after a certain date (accounting for weekends and scheduling conflicts), then

    Change your table structure to:

    Code:
    RoomID     Flw_Per    Flw_Date
    1234             1        6/8/2012
    1234             3        8/8/2012
    1234             6        11/8/2012
    2345             1        7/27/2012
        :
        :
    If the dates are always exactly 1, 3, 6 and 9 months out, then you only store
    the 1 month date, and you can calculate the others.

    Either way, your query just got a lot easier with the table structure above.

    Select RoomID, Flw_Per, flw_date
    from mytable
    where Flw_Date between #7/1/2012# and #7/31/2012#

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't hardcode values or make assumptions about follow intervals
    store the follow type as a code
    store the interval to the next followup, or better yet the code to the next review date, whether you do that as a date or number of days is irrelevant

    that way round if the numpty, sorry user, decides that the follow up interval on this item should be changed to monthly you are not reliant on hard coded values, but offset to the next review date
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2012
    Posts
    6

    Pulling Records based on user input

    Thank you for your response, the 1,3,6 and 9 months follow up date are based on a field finalinspection in another table Treatmenttable, for example if final inspection was done 07/01/2012, the 1 month follow up will be 08/01/2012 and 3 month follow up will be on 10/01/2012 and so on... the dates are always exactly 1, 3, 6 and 9 months out. So if i store 1 month how can i calculate the 3,6 and 9 month dates. i really appreciate all the help.

  5. #5
    Join Date
    Jul 2012
    Posts
    8
    I suggest you lookup the DateAdd function. It is quite simple to use. Here's a link to the official Microsoft page:
    DateAdd Function - Access - Office.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •