James Barash
james at fcidms.com
Mon Nov 2 14:14:45 CST 2009
I just had a similar situation except my table has only the End Dates for billing periods and I had to find orders based on their billing period. Assuming a table called rates, create a query like:
SELECT rates.Rate, rates.EffectiveDate, DateAdd("d",-1,nz(DMin("EffectiveDate","rates","[EffectiveDate]>#" & [EffectiveDate] & "#"),Int(Now()))) AS EndDate
FROM rates;
This does assume you do not enter any dates into the future since the last Effective Date range will be through today.
If you name that query ratesByPeriod and you have a table called vouchers, you can create a query:
Select vouchers.VoucherNumber, vouchers.Mileage * nz(ratesByPeriod.Rate,0) FROM vouchers, ratesByPeriod WHERE vouchers.VoucherDate Between ratesByPeriod.EffectiveDate And ratesByPeriod.EndDate;
As long as you don't have too many rate changes, this should work fine. The ratesByPeriod query will get slow if there are too many records in the underlying table.
Hope this helps.
James Barash
On Mon, Nov 2, 2009 at 1:14 PM, John Clark <John.Clark at niagaracounty.com> 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.
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
Gary Kjos
garykjos at gmail.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com