Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SQL Query, Can you embed a function call?
Is there a way to embed a function call in a SQL query? Please see my
code below. This runs fine if I omit the "fnCalculateGrossMargin( [Master$].PartNum, [Master$].Price) as GrossMargin " portion of the SQL query but get an error when I try to run it with. fnCalculateGrossMargin is a fairly complex function that can really only be done using a separate VBA function call. Thanks in advance. Best Regards, Dean CODE ********************************************* Public Sub rewriteFSFDataToWsht(strMeasConfig As String) On Error GoTo Err_rewriteFSFDataToWsht Dim rngTemp As Range Dim rstData As ADODB.Recordset Dim strConnection As String Dim strSQL As String ' Create the connection string. strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\FILEBOX\ProductivityTools\DFTTI.xls;" & _ "Extended Properties=Excel 8.0;" strSQL = "SELECT ALL " & _ "[Master$].Customer, " & _ "[Master$].PartNum, " & _ "[Master$].Qty AS Quantity, " & _ "fnCalculateGrossMargin( [Master$].PartNum, [Master$].Price) as GrossMargin " & _ "FROM [Master$] ;" Set rstData = New ADODB.Recordset rstData.Open strSQL, strConnection, adOpenForwardOnly, adLockReadOnly, adCmdText 'verify data were received, if so write to worksheet, if not warn the user If Not rstData.EOF Then Worksheets("FSF").Range("A5").CopyFromRecordset rstData Else MsgBox "No records returned.", vbCritical End If 'Clean up object and control variables rstData.Close Set rstData = Nothing Set rngTemp = Nothing Exit_rewriteFSFDataToWsht: Exit Sub Err_rewriteFSFDataToWsht: MsgBox "sub rewriteFSFDataToWsht " & Err.Description Resume Exit_rewriteFSFDataToWsht End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SQL Query, Embedding a Function Call | Excel Discussion (Misc queries) | |||
Basic query on PRODUCT function... | Excel Discussion (Misc queries) | |||
numerical integration | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
How to call a function from another workbook | Excel Discussion (Misc queries) |