calling module from query

[Jackie]

I am creating several reports and forms from a third party database. A
number of them require the balance due on them (original amount, minus
payments, plus interest, etc).

The writer of this program gave me the code to paste into a module for the
query to call (pasted at the end of this question). I pasted the code into a
module. I have not yet created a query to use it.

Now, when I try to do anything in this database, I get an error "Compile
Error, Expected list separator or)". I can't do anything in the database
without this error popping me into the debugger.

Two (and a half) questions:

1) Why am I getting this error and how can I fix it?

and

2) How do I tell a query to use this module?

Here's the code:

Public Function fnTotalNowDue(strTaxpayerID As String, strLiabilityNmbr
As String, strWarrantNmbr As String, _
dblTotal As Double, datFromDate As Date,
datThruDate As Date, dblInterestPerDiem As Double, _
Optional dblInterestAdj As Double, Optional
dblPayments As Double) As Double


Dim intDays As Integer
Dim db As Database
Dim rs As Recordset
Dim mySql As String
Dim dblAmount As Double

' Code Header inserted by the Procedure Header Add-In
'=============================================================
' mTotalDue.fnTotalNowDue
'-------------------------------------------------------------
' Purpose
' Author :
' Notes :
'-------------------------------------------------------------
' Parameters
'-----------
' [##] = Field Nbr on Agent Warrant File
Layout Document from DOR
' strTaxpayerID (String)- [2] - Taxpayer_TID ' strLiabilityNmbr
(String)- [4] - Liability_Nbr ' strWarrantNmbr (String)- [12] -
Warrant_Number ' dblTotal (Double)- [46] - Liability_Balance '
datFromDate (Date)- [8] - Notice_Interest_Date ' datThruDate (Date)- []
---- can be any date to calculate up to.
' dblInterestPerDiem (Double)- [47] - Per_Diem_Interest_Amt '
dblInterestAdj (Optional) (Double)- [] - ' dblPayments (Optional)
(Double)- [] -
'-------------------------------------------------------------
' Returns:
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 03-25-2003 RLK:
'=============================================================
' End Code Header block


On Error GoTo err_handler


mySql = "SELECT Sum([tbl_Payments.Payment_Amt]) AS SumOfPayment_Amt"
_
& " FROM tbl_Payments" _
& " GROUP BY tbl_Payments.Taxpayer_TID,
tbl_Payments.Liability_Nbr, tbl_Payments.Warrant_Number," _
& " tbl_Payments.paymentProcByState" _
& " HAVING (((tbl_Payments.Taxpayer_TID)='" & strTaxpayerID
& "')" _
& " AND ((tbl_Payments.Liability_Nbr)='" & strLiabilityNmbr
& "')" _
& " AND ((tbl_Payments.Warrant_Number)='" & strWarrantNmbr &
"')" _
& " AND ((tbl_Payments.paymentProcByState)=False));"
'debug.Print mySql
Set db = CurrentDb
Set rs = db.OpenRecordset(mySql)
If rs.EOF Then
dblPayments = 0
Else
dblPayments = rs!SumOfPayment_Amt
End If

intDays = DateDiff("d", datFromDate, datThruDate)
dblInterestAdj = dblInterestPerDiem * intDays
dblAmount = dblTotal - dblPayments + dblInterestAdj
fnTotalNowDue = (Int(dblAmount * 100)) / 100

exit_err_handler:
On Error Resume Next
rs.Close
Set db = Nothing
Exit Function

err_handler:
MsgBox "Error: " & Err.Number & vbLf & Err.Description
Resume exit_err_handler

End Function


Thanks for any help you can give! Jackie

 

[Randy Harris]

Jackie, the line breaks make your code pretty difficult to read. When you
get the compile error, Access should show you exactly which line caused the
error. Post that and someone will be able to tell you how to fix it.

 

[Jackie]

I understand what you're asking when you told me to post the part of the code
causing the problem, but that's the bizarre part ... unliike when code failed
in the past, NOTHING is highlighted when it goes to the debugger ... the
cursor isn't even on the screen. Any suggestions?