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

John Clark John.Clark at niagaracounty.com
Mon Nov 2 13:14:08 CST 2009


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.



More information about the AccessD mailing list