Using variables with SQL in VB

[Jay Easley]

I am trying to use a variable in an SQL statement, but I
cannot get Access to read the variable. Below is the
current statement that I have written.


Reports!bMP_Report_02_Task_Force_Bar_Chart!
Graph2.RowSource = "SELECT Manpower_Query.Location,Format
(Sum([Month_1])/173.2,'0.0') AS" & anyvariable & " FROM
Manpower_Query GROUP BY Manpower_Query.Location;"
DoCmd.Close

Any help would be appreciated.

thanks,
Jay Easley

 

[Wayne Morgan]

Reports!bMP_Report_02_Task_Force_Bar_Chart!Graph2.RowSource = "SELECT
Manpower_Query.Location, Format(Sum([Month_1])/173.2,'0.0') AS " & anyvariable & " FROM
Manpower_Query GROUP BY Manpower_Query.Location;"

You need a space between the AS and the ". When this is done, the value of AnyVariable
should be passed as part of the string, not the name of the variable. Is that what you are
wanting?

Example:
If AnyVariable = "XXX" then the SQL string would be

SELECT Manpower_Query.Location, Format(Sum([Month_1])/173.2,'0.0') AS XXX FROM
Manpower_Query GROUP BY Manpower_Query.Location;