changing a part of a query in VBA before execution

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

changing a part of a query in VBA before execution

Author

Topic

katarina07
Starting Member

31 Posts

Posted - 2006-05-02 : 06:09:56

Hi,

I have 8 CROSS-TAB QUERIES showing different types of loans/deposits, based on data from different tables.
They show BALANCE in VALUE FIELD for some product categories.
Now I would like to see also NUMBER OF ACCOUNTS in VALUE FIELD for these categories (I dont need to see both BALANCE and NUMBER OF ACCOUNTS at one time),
and was wondering if there is some other way to do it than having additional 8 queries with differing Value field.
Some solution so that the CROSS-TAB QUERY displays BALANCE in Value field at one time, and NUMBER OF ACCOUNTS at other time, according to what I need at the moment.

May be I could get a text of a query to VBA, and then replace the VALUE FIELD of a CROSS-TAB QUERY according to what I want at the moment - BALANCE or NUMBER OF ACCOUNTS, and after that execute the adjusted query with DoCmd.RunSQL
First of all, I dont know which functions returns the text of some query (I dont want to write the text in VBA directly, because the query changes from time to time, it would be easier just to get its text every time)

Still possible there is some completely other way to do it.
perhaps somebody was dealing with this kind of problem.

thanks
katarina

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-05-03 : 15:04:43

It is possible. I have done that.

Take a look at following sub from one of my module.
You use DAO.QueryDef to manupulate the sql query. My qryCustInvoice will return for one default invoice (say ID=123). Now my qryCustInvoiceParameters is almost identical to qryCustInvoice except it has a placeholder (I_NO)for a variable. I swap it out with the real value dynamically right before the report is rendered.

Public Sub ProcessInvoice(strTmp As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strQuery As String
Dim stDocName As String

On Error GoTo Err_ProcessInvoicw

Set db = currentdb
strQuery = "qryCustInvoice"
strSQL = currentdb.QueryDefs("qryCustInvoiceParameters").SQL
strTmp = Left(strTmp, Len(strTmp) - 1)
strTmp = Right(strTmp, Len(strTmp) - 14)
strSQL = Replace(strSQL, "I_NO", strTmp)
currentdb.QueryDefs(strQuery).SQL = strSQL

stDocName = "rptCustInvoice"
DoCmd.OpenReport stDocName, acPreview

End Sub

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources