SQL error message
I am using the following code. The code is programmed to pull a
mileage expense rate from a table called "ExpenseMileageRate" (the
fields are 'Rate' and 'EffectiveDate' and use it to calculate the
currency amount. As the rate changes, I need the code to lookup the
rate that was in effect on the date of the expense and use it on
several forms. Some of the forms are historical lookups, so I don't
want them all to lookup the amount currently in effect, but the rate
that was in effect on the date of the expense.
I am getting and error after the FROM and before "ExpenseMileageRate
ORDER BY. It highlights the table name and says Compile error:
Expected: End of Statement.
Does anyone know what I am doing wrong?? I am willing to change
anything to make this work.
Private Function GetRate(ExpenseDate As Date, MileageAmount As
Integer) As Currency
Dim db As DAO.Database
Dim rs As DAO.Recordset
sSQL = "SELECT DeloitteExpensesMileageRate.[EffectiveDate],
"DeloitteExpensesMileageRate ORDER BY DeloitteExpensesMileageRate.
Set db = CurrentDb()
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
rs.FindFirst ("MileageRate.[ChangeDate] <= #" & ExpenseDate & "#")
If Not rs.NoMatch Then
GetRate = MileageAmount * rs!Rate
GetRate = 0
Set rs = Nothing
Set db = Nothing