Can I place a function call withing SQL Statements?

Dear Excel/ VBA Expert

I am trying to import data from an MS Access database into my Excel Sheet.
Usually I have no problems with this process but today I need a VBA function in the SQL statement (I know I can do this with nested IIF()s but I want to know how to do this for future reference)

I have written the SQL statement in MS Excel
I have written the VBA function in MS Access because that is where the SQL is processed. Is my understanding correct in this regard?

ASIDE: Please comment on this.
(I just got an idea of doing this.... perhaps I can run a VBA function from within Access that has the SQL statement because I know it works in MS Access(I tested the below just in Access (not running the intial SQL in Excel)). However, why doesn't this work from within Excel?

'******The error Message is attached thanks.

' CODE IN MS Excel. Importing Data from access

SQLString = "SELECT  [Line_Item] & [Period] AS ID, Data_1.Market, Data_1.Line_Item, Data_1.Period, Data_1.Selling_Units_TY, Data_1.Selling_Units_YAGO, IIf([Selling_Units_YAGO]=0,'-',(([Selling_Units_TY]-[Selling_Units_YAGO])/[Selling_Units_YAGO]) * 100) AS [Vol Chg], IIf([Selling_Units_TY]=0,'-',[Dollars_TY]/[Selling_Units_TY]) AS Avg_Unit_Price, " _
                                & "Share_Percent_TY ([Selling_Units_TY],[Period], lngLATEST_52_WEEKS_TY,  lngYTD_TY,  lngLATEST_12_WEEKS_TY,  lngLATEST_4_WEEKS_TY) as Share_TY, Share_Percent_YAGO([Selling_Units_YAGO], [Period], lngLATEST_52_WEEKS_YAGO,  lngYTD_YAGO,  lngLATEST_12_WEEKS_YAGO, lngLATEST_4_WEEKS_YAGO) as Share_YAGO " _
                               & " FROM Data_1 " _
                               & " WHERE Data_1.Market= " & Chr(34) & Trim(theMarket) & Chr(34)

Set rs = db.OpenRecordset(SQLString, dbOpenDynaset)

'******************* These functions are in the MS Access mdb.
'Works for both Units and Dollars
Public Function Share_Percent_TY(lngDollars_TY As Long, strPeriod As String, lngLATEST_52_WEEKS_TY As Long, lngYTD_TY As Long, lngLATEST_12_WEEKS_TY As Long, lngLATEST_4_WEEKS_TY As Long) As Double
        Dim lngTotal As Long
        Dim dblShare_Percent_TY As Double

        Select Case strPeriod

        Case "LATEST 52 WEEKS"
                lngTotal = lngLATEST_52_WEEKS_TY
        Case "YTD"
                  lngTotal = lngYTD_TY
        Case "LATEST 12 WEEKS"
                  lngTotal = lngLATEST_12_WEEKS_TY
        Case "LATEST 4 WEEKS"
                  lngTotal = lngLATEST_4_WEEKS_TY
        End Select

        'Calculate the Percentage
        dblShare_Percent_TY = (lngDollars_TY / lngTotal) * 100

        Share_Percent_TY = dblShare_Percent_TY

End Function

'Works for both Units and Dollars
Public Function Share_Percent_YAGO(lngDollars_YAGO As Long, strPeriod As String, lngLATEST_52_WEEKS_YAGO As Long, lngYTD_YAGO As Long, lngLATEST_12_WEEKS_YAGO As Long, lngLATEST_4_WEEKS_YAGO As Long) As Double
        Dim lngTotal As Long
        Dim dblShare_Percent_YAGO As Double


        Select Case strPeriod

        Case "LATEST 52 WEEKS"
                lngTotal = lngLATEST_52_WEEKS_YAGO
        Case "YTD"
                  lngTotal = lngYTD_YAGO
        Case "LATEST 12 WEEKS"
                  lngTotal = lngLATEST_12_WEEKS_YAGO
        Case "LATEST 4 WEEKS"
                  lngTotal = lngLATEST_4_WEEKS_YAGO
        End Select

        'Calculate the Percentage
        dblShare_Percent_YAGO = (lngDollars_YAGO / lngTotal) * 100

        Share_Percent_YAGO = dblShare_Percent_YAGO

End Function

Your Help is much Appreciated.