Heyyas.
I am trying to print out a report which lists account details and payment amounts from january to december. I have following tables:
Payment(paymentNo, DateOwing, amount, accountID)
Account(accountID, Name, Address,.....)
The report is made using a query which just lists all the account details. Then i have 12 text boxes which go horizontally across from each account record. In these text boxes i would to list the amount paid that month (if any).
What i am trying to do is call a function GetPayment() from the control source of the text box. Inside the Function i have the following code:
'ddate is one of the 12 months of the year
Function GetPayment(ddate As String) As String
Dim str2SQL As String
str2SQL = "SELECT tblPayments.PaymentAmount "
str2SQL = str2SQL & " FROM tblPayments "
str2SQL = str2SQL & " WHERE tblPayments.YearOwing = '" & Reports!rptPayments.txtYear & "'"
str2SQL = str2SQL & " AND tblPayments.MonthOwing = '" & ddate & "'"
str2SQL = str2SQL & " AND tblPayments.AccountID = " & Reports!rptPayments.txtAccountID & ";"
GetPayment = str2SQL
End Function
This however just returns the sql statement as a string and dosent execute it. I have tried to execute the statement in the visual basic but as it is a select statement it does not like this. Is there any way to somehow run this sql statement and then just return the payment amount, or am i going about this entirely the wrong way?
In my search for answers i came across some ppl mentioning opening up a connection to a database and then geting a recordset from this connection however as my code is already in the database that is open i dont think this is going to work.
I really appreaciate any help or suggestions :)
Mike
