467,858 Members | 1,657 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,858 developers. It's quick & easy.

Extracting Data from embeded SQL select statement

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
Jan 24 '07 #1
  • viewed: 1793
Share:
1 Reply
Just wanted to say its all working now so nvm... for anyone who comes across a similar problem i used the following code:

Function GetPayment(ddate As String)

On Error GoTo Err_GetPayment

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 & ";"

Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()
Set rs = db.OpenRecordset(str2SQL)
rs.MoveFirst

GetPayment = rs![PaymentAmount]

Exit_GetPayment:
Exit Function
Err_GetPayment:
GetPayment = Null
Resume Exit_GetPayment

End Function


Cheers
Jan 25 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Porthos | last post: by
3 posts views Thread by Alfred | last post: by
7 posts views Thread by John Ortt | last post: by
7 posts views Thread by whitsey | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.