use of a function in access query not working

[jtrembla]

This is my function

Global GBL_ReportName As String
Global GBL_CompanyId As Integer
Global GBL_CompanyName As String
Global GBL_RegionId As Integer
Global GBL_RegionName As String
Global GBL_TherapyAreaId As Integer
Global GBL_TherapyAreaName As String
Global GBL_CompanyTherapyName As String


Public Function get_global(G_name As String)

     Select Case G_name
            Case "ReportName"
                    get_global = GBL_ReportName
            Case "CompanyName"
                    get_global = GBL_CompanyName
            Case "RegionName"
                    get_global = GBL_RegionName
            Case "CompanyId"
                    get_global = GBL_CompanyId
            Case "RegionId"
                    get_global = GBL_RegionId
            Case "TherapyAreaName"
                    get_global = GBL_TherapyAreaName
            Case "TherapyAreaId"
                    get_global = GBL_TherapyAreaId
            Case "CompanyTherapyName"
                    GBL_CompanyTherapyName = GBL_CompanyName & " " & GBL_TherapyAreaName
                    get_global = GBL_CompanyTherapyName
    End Select
    
End Function

Here is my query

SELECT T_Reports.RowName AS [get_Global("CompanyName")], T_Reports.[2003 $ (m)], T_Reports.[2004 $ (m)], T_Reports.[2005 $ (m)], T_Reports.[2006 $ (m)], T_Reports.[2007 $ (m)], T_Reports.[F 2008 $ (m)], T_Reports.[F 2009 $ (m)], T_Reports.[F 2010 $ (m)], T_Reports.[F 2011 $ (m)], T_Reports.[F 2012 $ (m)], T_Reports.[F 2013 $ (m)], T_Reports.[F 2014 $ (m)]
FROM T_Reports
WHERE (((T_Reports.CompanyName)=get_Global("CompanyName")) AND ((T_Reports.ReportName)=get_Global("ReportName")))
ORDER BY T_Reports.OrderId;

I am trying to transform the column name of RowName to the global variable company name. Unfornately, it is just outputting the name as get_Global("CompanyName"). I did a step through and it does not go into the function to try and grab the value.

Any help would be great.

 

[Remou]

That will not work, unfortunately. You can build the SQL string in code and either update an existing query or build a new query with the string.

 

[Golom]

I think you will need to build the SQL

Dim SQL As String

SQL = _
"SELECT RowName AS [" & get_Global("CompanyName") & "], " & _ 
"       [2003 $ (m)], [2004 $ (m)], [2005 $ (m)], " & _
"       [2006 $ (m)], [2007 $ (m)], [F 2008 $ (m)], " & _
"       [F 2009 $ (m)], [F 2010 $ (m)], [F 2011 $ (m)], " & _
"       [F 2012 $ (m)], [F 2013 $ (m)], [F 2014 $ (m)] " & _
"FROM T_Reports " & _
"WHERE CompanyName = " & get_Global("CompanyName") & " " & _
"  AND ReportName  = " & get_Global("ReportName") & " " & _
"ORDER BY OrderId; "

and then save that as the SQL for your query. Some parts of an SQL query cannot be modified dynamically as you are attempting to do. For example

SELECT TOP [red][Enter the Number][/red] ...

SELECT FieldName As [red][Enter An Alias][/red] ...

SELECT * FROM [red][Enter a Table Name][/red] ...

are not legal. You can call a function only where a user-entered parameter would be valid.

 

[jtrembla]

I was afraid of that...

Thanx, that suks, you would think microsoft would build support for that functionality. Now I have to spend more time building my sql.

 

[Golom]

Yeah ... but it doesn't suck all that bad

[green]... the above code ...[/green]

CurrentDb.TableDefs("myQuery").SQL = SQL

and now you can run it.

 

[jtrembla]

Thank you everyone.