Query by date for multiple ranges

[Xarkath]

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

[ssanfu]

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:

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:

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.

[Xarkath]

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.