473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Extracting Data from embeded SQL select statement

9
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
1 1956
mycall
9
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

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

Similar topics

6
by: dave | last post by:
I am trying to generate an email from the webpage using code below, which works fine. However I want to be able to include some dyanmic data how do I go about it ?can anybody point me in the...
4
by: Porthos | last post by:
Hi All, I've been working on mining data from a schema file (all attribute data so far) and have come to the point where I need to get information that is contained in tags. For instance,...
3
by: Alfred | last post by:
Hi I would like to extract only 15 records at a time from the backend in alfabetic order. Click on a button and then the next 15. Reason data must come over a 56k modem. The data is not...
7
by: John Ortt | last post by:
Hi there, I am trying to do a two level embeded query but it is causing Dr Watson errors. The line which is causing the problem is: CFCDeci: (Select sum (FlexChangeDeci) FROM as WHERE (....
7
by: | last post by:
Hello, Does anyone have an idea on how I can filter the data in the gridview control that was returned by an sql query? I have a gridview that works fine when I populate it with data. Now I...
4
by: Debbiedo | last post by:
My software program outputs an XML Driving Directions file that I need to input into an Access table (although if need be I can import a dbf or xls) so that I can relate one of the fields...
7
by: whitsey | last post by:
Help! I am trying to write an SQL statement (without much luck) to extract the total number of searches and orders from a particular customer, grouped into days/months/weeks (depending on the...
4
by: apatel85 | last post by:
Hey Guys, Total Number of Records (Based on 5 fields): 1000 Total Unique Records (Based on 5 Fields): 990 Total number of fields: 5 I have question regarding extracting duplicates from the...
2
by: sivadhanekula | last post by:
Hi Everyone A quick question: How to write MYSQL command in C-Language MYSQL Statement to select particular row from a table is: SELECT User FROM absprfg where User is the heading of the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.