Results 1 to 3 of 3
  1. #1
    Xarkath is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Millcreek WA
    Posts
    92

    Query by date for multiple ranges

    Hello,

    I am setting up a billing adjustment data base to track/record offline adjustments. The problem I am having is that rates change at different times for different clients and I do not want the end user to have to look up those rate changes each time they enter a record. I want them to just enter the effective date that the enrollment changed and have Access pull the rates approprite to that date



    Example:

    Eff date billing code rate Through Date
    01/01/2012 AM 5.00 05/31/2012
    01/01/2012 AD 3.00 05/31/2012
    06/01/2012 AM 5.00 12/31/2012
    06/01/2012 AD 3.50 12/31/2012
    01/01/2013 AM 5.25 Current
    01/01/2013 AD 3.50 Current


    Using this example what I need is a formula that will pull the rates for the most approprite eff date. So if the adjustment date is any date between 01/01/2013 and current the query should pull 5.25 and 3.50. If the adjustment date is any date between 06/01/12 and 12/31/12 then the query should pul 5.00 and 3.50 and any date prior to 06/01/12 should pull 5.00 and 3.00. There could easily be more and surely will be more dates spans added so this need to be flexible enough to handle that.

    I tried a few variations to set the criteria to where adjustment date is >= to the max eff date or adjustment date is between eff date and through date. I also tried a variation of that using the IFF statement but so far I either get errors or I get rates for multiple date spans. The date I am using for testing is 11/01/2012 so it should pull 5.00 and 3.50.

    If anyone knows the proper formula / code for this I would really appricate the knowlege.

    Thanks

    Rob

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I created a table, but replaced the spaces in your field names with underscores. Having spaces in object names is problematic.
    You also can't have "Current" in a date/time field. In my database, I typically set the "current" date to the beginning date plus 50 years.

    Here is the data set I used:
    Code:
    Eff_date    billing_code    rate    Through_Date
    01/01/2012    AM    5.00    05/31/2012
    01/01/2012    AD    3.00    05/31/2012
    06/01/2012    AM    5.00    12/31/2012
    06/01/2012    AD    3.50    12/31/2012
    01/01/2013    AM    5.25    01/01/2063
    01/01/2013    AD    3.50    01/01/2063
    Here is the SQL:
    Code:
    SELECT Billing.rate, Billing.billing_code, Billing.Eff_date, Billing.Through_Date
    FROM Billing
    WHERE (((Billing.Eff_date)<=#11/1/2012#) AND ((Billing.Through_Date)>=#11/1/2012#));
    Change the table name and the field names to your names.
    Of course, you could modify the SQL to reference a control on a form instead of hard coding the date in the WHERE clause.

  3. #3
    Xarkath is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Millcreek WA
    Posts
    92
    Thanks for the reply. I also found that some of the dates had been entered as mm/yyyy. So I added code to convert the through dates to a proper date format and "Current" to the system date. All the formulas are now working more or less how I had first thought they would.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-05-2013, 01:31 PM
  2. Multiple date ranges on report
    By AAA in forum Reports
    Replies: 1
    Last Post: 06-18-2012, 10:18 PM
  3. Query for gaps in date ranges
    By kagoodwin13 in forum Queries
    Replies: 6
    Last Post: 03-19-2012, 07:00 PM
  4. Query return for date ranges.
    By Desstro in forum Queries
    Replies: 2
    Last Post: 09-14-2010, 06:44 AM
  5. Select Query Using Different Date Ranges
    By Hemi426 in forum Queries
    Replies: 3
    Last Post: 07-26-2010, 01:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums