[AccessD] Query question ... Finding the proper rate/per date

John,

The problem is that a date is BETWEEN two dates.  So the rate table needs a "DteFrom" and a "DteTo". 
  When you create a new rate record, you need to lookup the last existing rate record and put 
yesterday's date in the DteTo field.  In the current rate record you need to put Today() plus 180 
(days into the future).

Now your query that looks up the rate has to lookup between two dates.

Or something like that.

John W. Colby
www.ColbyConsulting.com


John Clark wrote:
> Hello...been a long time since I've asked a question, but I am now trying to do something, that I've tried before, and I never could get it to work. I always find a work-around, but I want to actually do it right this time.
>  
> What I am trying to do is this...
>  
> I am creating a small program that creates witness vouchers for our District Attorney's dept. When a witness testifies in court, there is a flat fee of $15, and then mileage is added to that. The mileage rate can change, every 3 months, so I want the program to find the correct mileage rate, as per the date entered (the court date). Not only will this keep me from having to change the rate, every 3 months, but it would also allow the program to reprint any voucher, at any time, and keep its original rate. 
>  
> So, what I figured on doing...and please correct me, if I am going about this the wrong way...is to have a separate table for mileage rate. It would have 3 fields, a record ID (probably unneeded), effective date, and the rate for that date. I then want the program to look at the date of an entry, and match it up w/the proper date from this table.
>  
> I know this doesn't work...already tried it...but here is basically what it would be:
>  
> Max( < [Enter Date]) 
>  
> The entered date does indeed bring up the last date & rate entered, but it brings up ALL previous entries. So, if I could get the max date, it should be the last one...correct? But, this is not proper code.
>  
> This is a really small DB, but this one item is killing me. I'd like to use it for the flat fee table as well...it hasn't changed in a long time, but you never know.