If/then statement between dates

[nybaseball22]

Hello. I am trying to draft an if/then statement that will pull a
rate from a table and use it to multiply by a number from a different
table.

Table: ExpensesMain
Field: Mileage
Field: Date of Expense

Table: MileageRate
Field: EffetiveDate
Field: Rate

I am trying to add into my form (ExpensesAll) a string to an unbound
text box that will multiply the mileage by the mileage rate that was
effective on the date of the expense. There are several different
rates corresponding to their respective start dates in the MileageRate
table.

Any idea how I can make this work that will not be to difficult to
write and potentially add to other forms later? I am a novice at SQL,
so I can't get too technical.

Thanks.

 

[OldPro]

Hello. I am trying to draft an if/then statement that will pull a
rate from a table and use it to multiply by a number from a different
table.

Table: ExpensesMain
Field: Mileage
Field: Date of Expense

Table: MileageRate
Field: EffetiveDate
Field: Rate

I am trying to add into my form (ExpensesAll) a string to an unbound
text box that will multiply the mileage by the mileage rate that was
effective on the date of the expense. There are several different
rates corresponding to their respective start dates in the MileageRate
table.

Any idea how I can make this work that will not be to difficult to
write and potentially add to other forms later? I am a novice at SQL,
so I can't get too technical.

Thanks.

The tricky part is to find the right record in the MileageRate table.
You need to find the record that has a date less than or equal to the
date of the expense. If you just do a normal .findfirst, it could
find a much older record, whereas we want the very last record that
still meets the criteria. To do this, you will need a reverse sort.

Private Fucntion GetRate( ExpenseDate as DATE, ExpenseMiles as
integer) as currency
dim db as dao.database
dim rs as dao.recordset
sSQL = "SELECT MileageRate.[ChangeDate], MileageRate.[Rate] From
MileageRate ORDER BY MileageRate.[ChangeDate] DESC;"
set db = currentdb()
set rs = db.openrecordset(sSQL,dbOpenDynaset)
rs.findfirst("MileageRate.[ChangeDate] <= #" & ExpenseDate & "#")
if NOT rs.nomatch then
GetRate = ExpenseMiles*rs!Rate
else
GetRate = 0
endif
rs.close
set rs = nothing
db.close
set db = nothing
End Function

You will have to modify the above, if your dates include time;
10/27/07 won't ever equal 10/27/07 if one is at 1:00 pm and the other
is at 2:00 pm.