Hi, 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