SQL error message

[nybaseball22]

Hello,

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.[MileageRate]" From
"DeloitteExpensesMileageRate ORDER BY DeloitteExpensesMileageRate.
[EffectiveDate] DESC;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
rs.FindFirst ("MileageRate.[ChangeDate] <= #" & ExpenseDate & "#")
If Not rs.NoMatch Then
GetRate = MileageAmount * rs!Rate
Else
GetRate = 0
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function

Thanks

 

[George Nicholson]

quote-wise should be just:
sSQL = "SELECT ... DESC;"

i.e., get rid of the extra pair of quotes (on either side of FROM) in the
middle of your sSQL assignment. They serve no purpose (except to exclude the
FROM from sSQL) and would need concatenating ampersands even if they did.

 

[Dale Fye]

Your error message is because you are not wrapping the entire SQL string in
quotes. Word wrap may be part of the problem with this. When I build SQL
strings in my code, I try to keep each line relatively short, and use the
line wrap character (underscore) at the end of each line to make the code
more readable.

Rather than use the findfirst, I think I would write the SQL string to only
return a single value.

Try:

Private Function GetRate(ExpenseDate as Date, _
MileageAmount as Integer) as Currency

Dim rs as DAO.Recordset
Dim strCriteria as string

strSQL = "SELECT D1.MileageRate " _
& "FROM DeloitteExpensesMileageRage as D1 " _
& "WHERE D1.EffectiveDate = (SELECT MAX(D2.EffectiveDate) " _
& " FROM
DeloitteExpensesMileageRate as D2 " _
& "WHERE D2.EffectiveDate
<= #" _
& ExpenseDate & "#)"
Set rs = currentdb.openrecordset (strSQL)
if rs.eof then
msgbox "invalid date!"
GetRate = 0
else
GetRate = rs("MileageRate") * MileageAmount
endif

rs.close
set rs = nothing

End Function

HTH
Dale

 

[nybaseball22]

Dale,

I like this method better, thanks. However, I set this as On Current
for the form and get an error that the expression On Current has a
function containing the wrong number of arguements. Any idea what
this could be?

Thanks again.

 

[Dale Fye]

reformatted to make it a little more readable.

Private Function GetRate(ExpenseDate as Date, _
MileageAmount as Integer) as Currency

Dim rs as DAO.Recordset
Dim strCriteria as string

strSQL = "SELECT D1.MileageRate " _
& "FROM DeloitteExpensesMileageRage as D1 " _
& "WHERE D1.EffectiveDate = (SELECT MAX(D2.EffectiveDate) " _
& " FROM DeloitteExpensesMileageRate as D2 " _
& "WHERE D2.EffectiveDate <= #" & ExpenseDate & "#)"
Set rs = currentdb.openrecordset (strSQL)
if rs.eof then
msgbox "invalid date!"
GetRate = 0
else
GetRate = rs("MileageRate") * MileageAmount
endif

rs.close
set rs = nothing

End Function

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Your error message is because you are not wrapping the entire SQL string in
quotes. Word wrap may be part of the problem with this. When I build SQL
strings in my code, I try to keep each line relatively short, and use the
line wrap character (underscore) at the end of each line to make the code
more readable.

Rather than use the findfirst, I think I would write the SQL string to only
return a single value.

Try:

Private Function GetRate(ExpenseDate as Date, _
MileageAmount as Integer) as Currency

Dim rs as DAO.Recordset
Dim strCriteria as string

strSQL = "SELECT D1.MileageRate " _
& "FROM DeloitteExpensesMileageRage as D1 " _
& "WHERE D1.EffectiveDate = (SELECT MAX(D2.EffectiveDate) " _
& " FROM
DeloitteExpensesMileageRate as D2 " _
& "WHERE D2.EffectiveDate
<= #" _
& ExpenseDate & "#)"
Set rs = currentdb.openrecordset (strSQL)
if rs.eof then
msgbox "invalid date!"
GetRate = 0
else
GetRate = rs("MileageRate") * MileageAmount
endif

rs.close
set rs = nothing

End Function

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello,

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.[MileageRate]" From
"DeloitteExpensesMileageRate ORDER BY DeloitteExpensesMileageRate.
[EffectiveDate] DESC;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
rs.FindFirst ("MileageRate.[ChangeDate] <= #" & ExpenseDate & "#")
If Not rs.NoMatch Then
GetRate = MileageAmount * rs!Rate
Else
GetRate = 0
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function

Thanks

 

[Dale Fye]

What exactly does your OnCurrent code look like?

Dale

 

[nybaseball22]

I copied exactly as you have above and then I copied the second one
and replaced it. I inserted as a Private Function and then in the
OnCurrent I inserted =GetRate().

The error says I have an expression with the wrong number of
arguements.

Thanks

 

[George Nicholson]

Private Function GetRate(ExpenseDate as Date, _

MileageAmount as Integer) as Currency
..............

OnCurrent I inserted =GetRate().
Error: wrong number of arguments

The error is telling you *exactly* what the problem is. It should be:
GetRate(SomeDate,SomeInteger)

 

[nybaseball22]

I'm sorry, I don't understand. What do I need to change?

Thanks

 

[George Nicholson]

GetRate require 2 arguments. You need to supply them.

and then in the OnCurrent I inserted =GetRate().

"GetRate()" does not supply the 2 required arguments, which is what the
error message is telling you.

 

[Dale Fye]

If you are trying to fill in a textbox with the value from GetRate, you need
to pass it two values (a date and a mileage figure). You probably need to do
this in three places, the Forms Current event, and the AfterUpdate events of
the [ExpenseDate] and [Mileage] controls on your form. You need the error
checking in this code to make sure that you pass the correct type of data to
the function. Otherwise, you would need to change the data types of the
values you pass to the function to Variant, and check for Date/numeric data
types in that function.

1. The Forms current event.

Private Sub Form_Current

if not isdate(me.[ExpenseDate]) then
msgbox "not a valid date!"
me.txt_MileageReimbursement = NULL
elseif not isnumeric(me.[Mileage]) then
msgbox "not a valid mileage figure!"
me.txt_MileageReimbursement = NULL
else
me.txt_MileageReimbursement = GetRate([ExpenseDate], [Mileage])
endif

End Sub

2. For #2 and #3, you need to put this same line of code in the AfterUpdate
event of the [ExpenseDate] and [Mileage] controls on your form.

HTH
Dale

 

[nybaseball22]

That works perfect. Thank you all very much for the help.

 

[nybaseball22]

Ok, Can I change this a bit? I am using this in another form and
having a slight problem. I have this in the OnCurrent field, but when
I open the form, the Date is Null. Can I change the code for this
field to say if the date is null, do nothing. If it is not null, do
the code?

Thanks again

 

[Dale Fye]

Sure,

Add a line to the beginning of the if statement. Something like:

Private Sub Form_Current

if isnull(me.[ExpenseDate] Then
'don't display any error message
me.txt_MileageReimbursement = NUILL
elseif not isdate(me.[ExpenseDate]) then
msgbox "not a valid date!"
me.txt_MileageReimbursement = NULL
elseif not isnumeric(me.[Mileage]) then
msgbox "not a valid mileage figure!"
me.txt_MileageReimbursement = NULL
else
me.txt_MileageReimbursement = GetRate([ExpenseDate], [Mileage])
endif

End Sub

 

[nybaseball22]

This just keeps getting more complicated, but like I said, I am using
this code in several different places.

My recent problem is:

I am using this in a tabular form that has many different dates and
mileage amounts. I have inserted the code in the OnCurrent field of
the form, the AfterUpdate of the ExpenseDate field and the AfterUpdate
of the Mileage field. The problem is that the MileageAmount is
calculating based only on the first field of the form when it opens
and the last field updated after I change something and applying this
to every record on the form. Is there a way to have this apply
individually to each record on the form?

Thanks again.

 

[John W. Vinson]

The problem is that the MileageAmount is
calculating based only on the first field of the form when it opens
and the last field updated after I change something and applying this
to every record on the form. Is there a way to have this apply
individually to each record on the form?

Only if the value is stored in the Form's recordsource table. An unbound or
calculated control on a form - even a continuous form - is *there only once*,
even though it may appear repeatedly. Data is not stored in forms, but only in
tables; if you have a control on a form not bound to any table, and set its
value, then that's what you see (even if that control is displayed
repeatedly).

Do your calculation in the Query upon which the form is based, rather than in
code on the form, and you'll see individual record values.

John W. Vinson [MVP]

 

[nybaseball22]

Thanks John. Based on the code I have above, do you know how I could
change this to fit into my query called DeloitteExpensesNotSub?

 

[Dale Fye]

Add a new column to the query:

MileageReimbursement: IIF(ISNULL([EffectiveDate]) OR ISNULL([Mileage]),
NULL, GetRate([EffectiveDate], [Mileage]))

Watch the word wrap.
Dale

 

[nybaseball22]

I tried this but got an error of Undefined Function GetRate. I added
a Private Function GetRate to the Form but that didn't work. Where do
I need to place the GetRate to make it work?

Thanks

 

[George Nicholson]

GetRate needs to be a *Public* function in a general/regular code module. A
"Regular" module is one that is not attached to a class object (i.e., Form
or Report).

In the VB editor: Insert>Module. Name the module "modGeneral" (or anything
other than GetRate), place the function in that module and delete it from
anywhere else you might have placed it. You only want one of them.