How do I assign a MS Access query column name to a global variable.

I need to pass a global variable to a query column name so that my report legend automatically labels the data correctly.

I can declare a global variable that is accessible to my queries in other ways, but I've never tried to do so for a column name and I suspect their is some magical use of special characters, quotes, and brackets that will accomplish this.... right?  Since the brackets alone simply uses everything inside the brackets as the column name, I'm hoping there is some syntax I can use in the SQL statement to go get my global variable set as the column name instead.

Public Function get_global254(G_name As String) As Variant

     Select Case G_name
            Case "MetricValue1"
                    get_global254 = MetricValue1  'query format: get_global254("MetricValue1")
          
    End Select
End Function

Open in new window


I want to set the value of MetricValue1 when the user opens a form that has this value set to their choosing.

Then I want to have the column for the data produced in a query to have that name.  Instead of just using AS [Value 1], and make the use look up what they decided to call Value 1, I've assigned it to MetricValue1 as a global string, and would like it to appear as the column name as I'm trying to do below see:  Sum(Tbl_MetricData.Value) AS  get_global254("MetricValue1"),


SELECT Tbl_MetricData.ValueDate, Sum(Tbl_MetricData.Goal) AS Goal, Sum(Tbl_MetricData.Value) AS  get_global254("MetricValue1"), Sum(Tbl_MetricData.UpperLimit) AS [Upper Limit], Sum(Tbl_Metrics.MetricLowerLimit) AS [Lower Limit]
FROM Tbl_MetricData INNER JOIN Tbl_Metrics ON Tbl_MetricData.MetricID = Tbl_Metrics.MetricsID
WHERE (((Tbl_Metrics.MetricsID)=[Forms]![Frm_ProcessMetricsProjects]![MetricsID]) AND ((Tbl_MetricData.ValueDate)>[Forms]![Frm_ProcessMetricsProjects]![MetricStartDate] And (Tbl_MetricData.ValueDate)<[Forms]![Frm_ProcessMetricsProjects]![MetricEndDate])) OR (((Tbl_Metrics.MetricsID)=[Forms]![Frm_ProcessMetricsProjects]![MetricsID]) AND ((Tbl_MetricData.ValueDate)=[Forms]![Frm_ProcessMetricsProjects]![MetricStartDate] Or (Tbl_MetricData.ValueDate)=[Forms]![Frm_ProcessMetricsProjects]![MetricEndDate]))
GROUP BY Tbl_MetricData.ValueDate
ORDER BY Tbl_MetricData.ValueDate;
David SmithsteinCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Set the SQL property of the query:

Dim q as DAO.QueryDef
Dim Sql As String

Sql = "your long SQL including the modified columns name (alias)

Set q = CurrentDb.QueryDefs("YourQuery")
q.SQL = Sql

' Open/use the query.

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
The "AS  get_global254("MetricValue1")" in your query will not work.  Instead, use some arbitrary name (plain text) in your query as a place holder, like this:


Sum(Tbl_MetricData.Value) AS MyTemporaryColumnName

Open in new window



Then as Gustav suggested, work with the SQL property of the query def.  However, instead of putting the lengthy SQL statement into VBA, I think you can do something like this:


Dim qd as DAO.QueryDef
dim sColumnHeader as string

Set qd = CurrentDb.QueryDefs("YourQueryName")
sColumnHeader  = get_global254("MetricValue1")
qd.SQL =  Replace(qd.SQL, "MyTemporaryColumnName", sColumnHeader) 

Open in new window

0
mbizupCommented:
Although with that approach, you would have to use some method to save the original sql and restore it after running the report (otherwise the Replace would only work once).
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If your working with a querydef, setting the caption property for the column should work as well.   That way your not modifying the SQL statement.   Might save you a bit in that the query won't be recosted when executed.

Jim.
1
David SmithsteinCEOAuthor Commented:
Thanks Jim!  

I like the caption property strategy, how do I reference it in VBA?  Probably by column position, right? Does Access start the count at 0, like it does for drop down list form controls?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sorry for the delay...meant to get back to this Friday.  I'll work up an example here in a bit.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK, here's the code roughed out:

Sub Test()

  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim prp As DAO.Property
  
  Set db = CurrentDb()
  
  Set qdf = db.QueryDefs("Query1")
  
  Err = 0
  On Error Resume Next
  
  qdf.Fields(2).Properties("Caption") = "myColumnName2"
  
  
  If Err.Number = 3270 Then
    On Error GoTo 0
    Set prp = qdf.CreateProperty("Caption", dbText, "myColumnName")
    qdf.Fields(2).Properties.Append prp
  End If
  
  Set prp = Nothing
  
  qdf.Close
  Set qdf = Nothing
  
  Set db = Nothing
   
End Sub

Open in new window


 Let me know if you need anything else.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and a couple quick notes on that:

1.  The caption property of the fields in a querydef are user defined ones.   They don't exist by default, so the code tests for an error and adds it to the properties collection if it does not exist.

2. The code above works with the fields by ordinal number 'Fields(2)'.    You can use that (with the correct number of course) or the column name.

Jim.
0
David SmithsteinCEOAuthor Commented:
Thanks Jim!!!

I was trying to find the syntax for refencing the query columns and was coming up empty.  I'll give this a try.  I have it working with a redefinition of the whole query in VBA, but with several queries to do for a given record, it's slowing down the UI a little, hopefully this will be a little faster.  Thanks again! - David
0
David SmithsteinCEOAuthor Commented:
Hi Jim, so I went to implement your code, and it works for a single query assignment, but I have to do this for 5 queries for a given record before the user looks at any of the graphs possible on that record.  I'm getting the error, "Cannot append. An object with that name already exists in the collection."  I'm not quite sure what I'm duplicating that I'm not supposed to.

The code below is how I'm opening the form so that all the query captions are set prior to the display of any graphs on a subform being established trying to use your approach.

Public Sub OpenMetricToGraph2(MetricID As String)
On Error GoTo Err_OpenMetricToGraph2

    Dim stDocName As String
    Dim stLinkCriteria As String
    
    Dim db As DAO.Database
    
    Dim qdf1 As DAO.QueryDef
    Dim qdf2 As DAO.QueryDef
    Dim qdf3 As DAO.QueryDef
    Dim qdf4 As DAO.QueryDef
    Dim qdf5 As DAO.QueryDef
    
    
    Dim prp1 As DAO.Property
    Dim prp2 As DAO.Property
     
    stDocName = "Frm_ProcessMetricsProjects"
    get_global66 ("MetricsIDGlb")
     
     MetricValue1 = Nz(DLookup("[value1]", "v_metricvalues", "[MetricsID]=" & MetricID), "Not Assigned")
     MetricValue2 = Nz(DLookup("[value2]", "v_metricvalues", "[MetricsID]=" & MetricID), "Not Assigned")
     MetricValue3 = Nz(DLookup("[value3]", "v_metricvalues", "[MetricsID]=" & MetricID), "Not Assigned")
     
     
     'MetricsIDGlb
     MetricsIDGlb = MetricID
     
  Set db = CurrentDb()
  

  
  Set qdf1 = db.QueryDefs("Qry_MetricsGraphForm")
  Set qdf2 = db.QueryDefs("Qry_MetricGraph")
  Set qdf3 = db.QueryDefs("Qry_MetricsGraphForm3")
  Set qdf4 = db.QueryDefs("Qry_MetricsGraphNoLimitsForm")
  Set qdf5 = db.QueryDefs("Qry_MetricsGraphFormAllValues")
  
  

    Set prp1 = qdf1.CreateProperty("Caption1", dbText, MetricValue1)
    qdf1.Fields(2).Properties.Append prp1
 
    Set prp2 = qdf2.CreateProperty("Caption2", dbText, MetricValue1)
    qdf2.Fields(2).Properties.Append prp2
  
  
  Set prp1 = Nothing
  Set prp2 = Nothing
  
  qdf1.Close
  qdf2.Close
  
  Set db = Nothing

        
    If isloaded("Frm_ProcessMetricsProjects") = True Then
        DoCmd.Close acForm, "Frm_ProcessMetricsProjects"
   End If
         
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Form_Frm_ProcessMetricsProjects.SetFocus
   
    DoCmd.Restore
    
    Call UserActivity

Exit_OpenMetricToGraph2:
    Exit Sub

Err_OpenMetricToGraph2:
     MsgBox Err.Description
    Resume Exit_OpenMetricToGraph2
End Sub

Open in new window


This code works for multiple queries, but I'm hoping your approach might be a little faster.

Public Sub OpenMetricToGraph(MetricID As String)
On Error GoTo Err_OpenMetricToGraph

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Frm_ProcessMetricsProjects"
    get_global66 ("MetricsIDGlb")
     
     MetricValue1 = Nz(DLookup("[value1]", "v_metricvalues", "[MetricsID]=" & MetricID), "Not Assigned")
     MetricValue2 = Nz(DLookup("[value2]", "v_metricvalues", "[MetricsID]=" & MetricID), "Not Assigned")
     MetricValue3 = Nz(DLookup("[value3]", "v_metricvalues", "[MetricsID]=" & MetricID), "Not Assigned")
     
     
     'MetricsIDGlb
     MetricsIDGlb = MetricID
     
        Dim q As DAO.QueryDef
        Dim Sql As String
        
        
        
        Sql = "SELECT Tbl_MetricData.ValueDate, Sum(Tbl_MetricData.Goal) AS Goal, Sum(Tbl_MetricData.Value) AS [" & get_global254("MetricValue1") & "], Sum(Tbl_MetricData.UpperLimit) AS [Upper Limit], Sum(Tbl_Metrics.MetricLowerLimit) AS [Lower Limit] FROM Tbl_MetricData INNER JOIN Tbl_Metrics ON Tbl_MetricData.MetricID = Tbl_Metrics.MetricsID WHERE (((Tbl_Metrics.MetricsID) = [Forms]![Frm_ProcessMetricsProjects]![MetricsID]) And ((Tbl_MetricData.ValueDate) > [Forms]![Frm_ProcessMetricsProjects]![MetricStartDate] And (Tbl_MetricData.ValueDate) < [Forms]![Frm_ProcessMetricsProjects]![MetricEndDate])) Or (((Tbl_Metrics.MetricsID) = [Forms]![Frm_ProcessMetricsProjects]![MetricsID]) And ((Tbl_MetricData.ValueDate) = [Forms]![Frm_ProcessMetricsProjects]![MetricStartDate] Or (Tbl_MetricData.ValueDate) = [Forms]![Frm_ProcessMetricsProjects]![MetricEndDate])) GROUP BY Tbl_MetricData.ValueDate ORDER BY Tbl_MetricData.ValueDate;"
        Set q = CurrentDb.QueryDefs("Qry_MetricsGraphForm")
        q.Sql = Sql
        
       Sql = "SELECT Tbl_MetricData.ValueDate, Sum(Tbl_MetricData.Goal) AS Goal, Sum(Tbl_MetricData.Value) AS [" & get_global254("MetricValue1") & "], Sum(Tbl_MetricData.UpperLimit) AS [Upper Limit], Sum(Tbl_Metrics.MetricLowerLimit) AS [Lower Limit] FROM Tbl_MetricData INNER JOIN Tbl_Metrics ON Tbl_MetricData.MetricID = Tbl_Metrics.MetricsID WHERE (((Tbl_Metrics.MetricsID) = [Forms]![Frm_ProcessMetricsProjects]![MetricsID]) And ((Tbl_MetricData.ValueDate) > [Forms]![Frm_ProcessMetricsProjects]![MetricStartDate] And (Tbl_MetricData.ValueDate) < [Forms]![Frm_ProcessMetricsProjects]![MetricEndDate])) Or (((Tbl_Metrics.MetricsID) = [Forms]![Frm_ProcessMetricsProjects]![MetricsID]) And ((Tbl_MetricData.ValueDate) = [Forms]![Frm_ProcessMetricsProjects]![MetricStartDate] Or (Tbl_MetricData.ValueDate) = [Forms]![Frm_ProcessMetricsProjects]![MetricEndDate])) GROUP BY Tbl_MetricData.ValueDate ORDER BY Tbl_MetricData.ValueDate;"
       Set q = CurrentDb.QueryDefs("Qry_MetricGraph")
       q.Sql = Sql
      
      
        Sql = "SELECT Tbl_MetricData.ValueDate, Sum(Tbl_MetricData.Goal) AS Goal, Sum(Tbl_MetricData.Value2) AS [" & get_global256("MetricValue3") & "], Sum(Tbl_MetricData.UpperLimit) AS [Upper Limit], Sum(Tbl_Metrics.MetricLowerLimit) AS [Lower Limit] FROM Tbl_MetricData INNER JOIN Tbl_Metrics ON Tbl_MetricData.MetricID = Tbl_Metrics.MetricsID WHERE (((Tbl_Metrics.MetricsID) = [Forms]![Frm_ProcessMetricsProjects]![MetricsID]) And ((Tbl_MetricData.ValueDate) > [Forms]![Frm_ProcessMetricsProjects]![MetricStartDate] And (Tbl_MetricData.ValueDate) < [Forms]![Frm_ProcessMetricsProjects]![MetricEndDate])) Or (((Tbl_Metrics.MetricsID) = [Forms]![Frm_ProcessMetricsProjects]![MetricsID]) And ((Tbl_MetricData.ValueDate) = [Forms]![Frm_ProcessMetricsProjects]![MetricStartDate] Or (Tbl_MetricData.ValueDate) = [Forms]![Frm_ProcessMetricsProjects]![MetricEndDate])) GROUP BY Tbl_MetricData.ValueDate ORDER BY Tbl_MetricData.ValueDate;"
        Set q = CurrentDb.QueryDefs("Qry_MetricsGraphForm3")
        q.Sql = Sql
        
        Sql = "SELECT Tbl_MetricData.ValueDate, Sum(Tbl_MetricData.Goal) AS Goal, Sum(Tbl_MetricData.Value) AS [" & get_global254("MetricValue1") & "] FROM Tbl_MetricData INNER JOIN Tbl_Metrics ON Tbl_MetricData.MetricID = Tbl_Metrics.MetricsID WHERE (((Tbl_Metrics.MetricsID)=[Forms]![Frm_ProcessMetricsProjects]![MetricsID]) AND ((Tbl_MetricData.ValueDate)>[Forms]![Frm_ProcessMetricsProjects]![MetricStartDate] And (Tbl_MetricData.ValueDate)<[Forms]![Frm_ProcessMetricsProjects]![MetricEndDate])) OR (((Tbl_Metrics.MetricsID)=[Forms]![Frm_ProcessMetricsProjects]![MetricsID]) AND ((Tbl_MetricData.ValueDate)=[Forms]![Frm_ProcessMetricsProjects]![MetricStartDate] Or (Tbl_MetricData.ValueDate)=[Forms]![Frm_ProcessMetricsProjects]![MetricEndDate])) GROUP BY Tbl_MetricData.ValueDate ORDER BY Tbl_MetricData.ValueDate;"
        Set q = CurrentDb.QueryDefs("Qry_MetricsGraphNoLimitsForm")
        q.Sql = Sql
        
        Sql = "SELECT Tbl_MetricData.ValueDate, Sum(Tbl_MetricData.Goal) AS Goal, Sum(Tbl_MetricData.Value) AS [" & get_global254("MetricValue1") & "], Sum(Tbl_MetricData.Value1) AS [" & get_global255("MetricValue2") & "], Sum(Tbl_MetricData.Value2) AS [" & get_global256("MetricValue3") & "] FROM Tbl_MetricData INNER JOIN Tbl_Metrics ON Tbl_MetricData.MetricID = Tbl_Metrics.MetricsID WHERE (((Tbl_Metrics.MetricsID)=[Forms]![Frm_ProcessMetricsProjects]![MetricsID]) AND ((Tbl_MetricData.ValueDate)>[Forms]![Frm_ProcessMetricsProjects]![MetricStartDate] And (Tbl_MetricData.ValueDate)<[Forms]![Frm_ProcessMetricsProjects]![MetricEndDate])) OR (((Tbl_Metrics.MetricsID)=[Forms]![Frm_ProcessMetricsProjects]![MetricsID]) AND ((Tbl_MetricData.ValueDate)=[Forms]![Frm_ProcessMetricsProjects]![MetricStartDate] Or (Tbl_MetricData.ValueDate)=[Forms]![Frm_ProcessMetricsProjects]![MetricEndDate])) GROUP BY Tbl_MetricData.ValueDate ORDER BY Tbl_MetricData.ValueDate;"
        Set q = CurrentDb.QueryDefs("Qry_MetricsGraphFormAllValues")
        q.Sql = Sql
        
        
    If isloaded("Frm_ProcessMetricsProjects") = True Then
        DoCmd.Close acForm, "Frm_ProcessMetricsProjects"
   End If
         DoCmd.OpenForm stDocName, , , stLinkCriteria
          Form_Frm_ProcessMetricsProjects.SetFocus
   
    DoCmd.Restore
    Call UserActivity

Exit_OpenMetricToGraph:
    Exit Sub

Err_OpenMetricToGraph:
     MsgBox Err.Description
    Resume Exit_OpenMetricToGraph
End Sub

Open in new window

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
  Set db = CurrentDb()
  
  '
  ' Open first query def and set the caption for field(x)
  '
  Set qdf = db.QueryDefs("Qry_MetricsGraphForm")
  
  ' Try to set the property.   If it exists, everything will be fine.
  ' If it doesn't exist, we'll get error 3270 and need to create it.
  Err = 0
  On Error Resume Next
  qdf.Fields(x).Properties("Caption") = MetricValue1
    
  If Err.Number = 3270 Then
    On Error GoTo 0
    Set prp = qdf.CreateProperty("Caption", dbText, MetricValue1)
    qdf.Fields(x).Properties.Append prp
  End If
  
  Set prp = Nothing
  
  qdf.Close
  Set qdf = Nothing
  
  '
  ' Open second query def and set the caption for field(x)
  '
  Set qdf = db.QueryDefs("Qry_MetricGraph")
  
  ' Try to set the property.   If it exists, everything will be fine.
  ' If it doesn't exist, we'll get error 3270 and need to create it.
  Err = 0
  On Error Resume Next
  qdf.Fields(x).Properties("Caption") = MetricValue1
    
  If Err.Number = 3270 Then
    On Error GoTo 0
    Set prp = qdf.CreateProperty("Caption", dbText, MetricValue1)
    qdf.Fields(x).Properties.Append prp
  End If
  
  Set prp = Nothing
  
  qdf.Close
  Set qdf = Nothing

  '
  ' repeat for as many querydefs as you need.
  '

  ' Now clean-up
  Set db = Nothing

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.