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.