What's wrong with my code? (1 Viewer)

[gddrew]

When the user clicks a button on a form, I want this SQL to execute:

    DoCmd.RunSQL "SELECT tblOpPlanForecast.ProjNum, tblOpPlanForecast.DevCenter, " & _
    "Sum(tblOpPlanForecast.DecRes) AS DecActFTE, Sum(tblOpPlanForecast.SumOfDecODC) AS DecActODC, " & _
    "Sum(Nz(tblOpPlanForecast12.DecRes)) AS DecFCFTE, Sum(Nz(tblOpPlanForecast12.SumOfDecODC)) AS DecFCODC INTO tblTT4 " & _
    "FROM tblOpPlanForecast LEFT JOIN tblOpPlanForecast12 ON (tblOpPlanForecast.DevCenter = tblOpPlanForecast12.DevCenter) " & _
    "AND (tblOpPlanForecast.ProjNum = tblOpPlanForecast12.ProjNum) " & _
    "GROUP BY tblOpPlanForecast.ProjNum, tblOpPlanForecast.DevCenter;"

Given that name such as "DecActFTE" and "tblOpPlanForecast12" vary from month to month, I want the user to select a value from a drop-down menu, and the code will execute based on the selection. Rather than having to write the query 12 times and call one of 12 available queries, it seems more efficient to do simply specify the variables "DecActFTE" and "tblOpPlanForecast12" based on the selection. Thus I tried to write my query like this and I keep getting syntax errors:

    Dim strNumber As String
    Dim strName As String
    strNumber = cboMonthNumber.Value
    strName = txtMonth.Value

    DoCmd.RunSQL "SELECT tblOpPlanForecast.ProjNum, tblOpPlanForecast.DevCenter, " & _
    "Sum(tblOpPlanForecast."&strName&"Res) AS "&strName&"ActFTE, Sum(tblOpPlanForecast.SumOf"&strName&"ODC) AS "&strName&"ActODC, " & _
    "Sum(Nz(tblOpPlanForecast"&strNumber&"."&strName&"Res)) AS "&strName&"FCFTE, Sum(Nz(tblOpPlanForecast"&strNumber&".SumOf"&strName&"ODC)) AS "&strName&"FCODC INTO tblTT4 " & _
    "FROM tblOpPlanForecast LEFT JOIN tblOpPlanForecast"&strNumber&" ON (tblOpPlanForecast.DevCenter = tblOpPlanForecast"&strNumber&".DevCenter) " & _
    "AND (tblOpPlanForecast.ProjNum = tblOpPlanForecast"&strNumber&".ProjNum) " & _
    "GROUP BY tblOpPlanForecast.ProjNum, tblOpPlanForecast.DevCenter;"

I know this is the right way to go, but I can't figure out what I'm doing wrong. I'm trusting the experts here know the answer!

 

[pbaldy]

The way to figure out these types of problems is to use a string variable for your SQL, so you can print it and check it out. Add/modify the following

Dim strSQL as String
strSQL = "SELECT..."
Debug.Print strSQL
DoCmd.RunSQL strSQL

in the Immediate window will appear the finished SQL. Comparing that to your working SQL should reveal the problem.

 

[petehilljnr]

AS "&strName&"ActFTE[CODE]

should be

[CODE]AS " & strName & "ActFTE[CODE]

Note the spaces.

Regards,
Pete

 

[gddrew]

Ok, I've tried runing the most basic query using this syntax and I get an error.

    Dim strSQL As String
    strSQL = "SELECT tblOpPlanForecast.ProjNum, " & _
    "tblOpPlanForecast.DevCenter, " & _
    "tblOpPlanForecast.JanRes AS JanActFTE " & _
    "FROM tblOpPlanForecast;"

    Debug.Print strSQL
    DoCmd.RunSQL strSQL

This results in:

A RunSQL action requires an argument consisting of an SQL statement.

 

[unclejoe]

Hi,

RunSQL is Action Query. Example "DELETE", "UPDATE", "INSERT"

Your SELECT query will not work. Thus, the error message.

What you want to do with this record?

P.S. What was the result in the Immediate Window of your SQL Syntax?

Ok, I've tried runing the most basic query using this syntax and I get an error.

    Dim strSQL As String
    strSQL = "SELECT tblOpPlanForecast.ProjNum, " & _
    "tblOpPlanForecast.DevCenter, " & _
    "tblOpPlanForecast.JanRes AS JanActFTE " & _
    "FROM tblOpPlanForecast;"

    Debug.Print strSQL
    DoCmd.RunSQL strSQL

This results in:

A RunSQL action requires an argument consisting of an SQL statement.

 

[gddrew]

I changed it to the code below, but nothing happens:

Dim strSQL As String
    strSQL = "SELECT tblOpPlanForecast.ProjNum, " & _
    "tblOpPlanForecast.DevCenter, " & _
    "tblOpPlanForecast.JanRes AS JanActFTE " & _
    "FROM tblOpPlanForecast;"

    Debug.Print strSQL

Then I learned that Immediate Window refers to another pane in the VBA screen. Alt+G to view that window.

 

[kidrobot]

It won't really run your code per se, but it will help you find what is wrong with it. So what I'm trying to say is make it do something and see if it works.

 

[gddrew]

Here's my code based on what I've learned from those who've contributed here:

    Dim strNumber As String
    Dim strName As String
    strNumber = cboMonthNumber.Value
    strName = txtMonthName.Value

    DoCmd.RunSQL "SELECT tblOpPlanForecast.ProjNum, " & _
    "tblOpPlanForecast.DevCenter, " & _
    "Sum(tblOpPlanForecast." & strName & "Res) AS " & strName & "ActFTE" & _
    "Sum(tblOpPlanForecast.SumOf" & strName & "ODC) AS " & strName & "ActODC, " & _
    "Sum(Nz(tblOpPlanForecast" & strNumber & "." & strName & "Res)) AS " & strName & "FCFTE, " & _
    "Sum(Nz(tblOpPlanForecast" & strNumber & ".SumOf" & strName & "ODC)) AS " & strName & "FCODC INTO tblTT4 " & _
    "FROM tblOpPlanForecast LEFT JOIN tblOpPlanForecast" & strNumber & " ON (tblOpPlanForecast.DevCenter = tblOpPlanForecast" & strNumber & ".DevCenter) " & _
    "AND (tblOpPlanForecast.ProjNum = tblOpPlanForecast" & strNumber & ".ProjNum) " & _
    "GROUP BY tblOpPlanForecast.ProjNum, tblOpPlanForecast.DevCenter;"

Using the debug technique (thanks, pbaldy), the above code results in this SQL statement, which is what I wanted:

SELECT tblOpPlanForecast.ProjNum, tblOpPlanForecast.DevCenter, Sum(tblOpPlanForecast.JanRes) AS JanActFTESum(tblOpPlanForecast.SumOfJanODC) AS JanActODC, Sum(Nz(tblOpPlanForecast01.JanRes)) AS JanFCFTE, Sum(Nz(tblOpPlanForecast01.SumOfJanODC)) AS JanFCODC INTO tblTT4 FROM tblOpPlanForecast LEFT JOIN tblOpPlanForecast01 ON (tblOpPlanForecast.DevCenter = tblOpPlanForecast01.DevCenter) AND (tblOpPlanForecast.ProjNum = tblOpPlanForecast01.ProjNum) GROUP BY tblOpPlanForecast.ProjNum, tblOpPlanForecast.DevCenter;

But when I actually try to execute it I get the message "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

 

[pbaldy]

Notice anything wrong?

...AS JanActFTESum(tblOpPlanForecast.SumOfJanODC)...

 

[boblarson]

Notice anything wrong?

...AS JanActFTESum(tblOpPlanForecast.SumOfJanODC)...

ooh! ooh! Can I get "spaced out?" (oh, I'm already that )

 

[kidrobot]

DoCmd.RunSQL strSQL


As said before you can't DoCmd.RunSQL to a select query so set that equal to a string.

 

[pbaldy]

As said before you can't DoCmd.RunSQL to a select query

OP's last post was not a SELECT query.

 

[kidrobot]

DoCmd.RunSQL "SELECT tblOpPlanForecast.ProjNum, " & _
"tblOpPlanForecast.DevCenter, " & _......

That looks like a select query to me..

 

[gddrew]

That looks like a select query to me..

Yes, though at the end it does say "INTO tblTT4". My other queries written this way do work.

 

[boblarson]

Yes, a Select ...Into is a type that works with DoCmd.RunSQL.

Now, if no one is going to get it from my post directly after Paul's hint, I'll say it explicitly. A SPACE IS REQUIRED here, between these two letters:
...AS JanActFTESum(tblOpPlanForecast.SumOfJanODC)...

 

[gddrew]

Now, if no one is going to get it from my post directly after Paul's hint, I'll say it explicitly. A SPACE IS REQUIRED here, between these two letters:
...AS JanActFTESum(tblOpPlanForecast.SumOfJanODC)...

Yes, Bob, I got the hint

Many thanks to everyone here for their kind assistance. In the interest of sharing the knowledge, here's my query, which executes perfectly.

Dim strNumber As String
Dim strName As String
strNumber = cboMonthNumber.Value
strName = txtMonthName.Value

DoCmd.RunSQL "SELECT tblOpPlanForecast.ProjNum, tblOpPlanForecast.DevCenter, " & _
"Sum(tblOpPlanForecast." & strName  &  "Res) AS " & strName  &  "ActFTE, " & _
"Sum(tblOpPlanForecast.SumOf" & strName  &  "ODC) AS " & strName  &  "ActODC, " & _
"Sum(Nz(tblOpPlanForecast" & strNumber  &  "." & strName  &  "Res)) AS " & strName  &  "FCFTE, " & _
"Sum(Nz(tblOpPlanForecast" & strNumber  &  ".SumOf" & strName  &  "ODC)) AS " & strName  &  "FCODC INTO tblTT4 " & _
"FROM tblOpPlanForecast LEFT JOIN tblOpPlanForecast" & strNumber  &  " ON (tblOpPlanForecast.DevCenter = tblOpPlanForecast" & strNumber  &  ".DevCenter) " & _ 
"AND (tblOpPlanForecast.ProjNum = tblOpPlanForecast" & strNumber  &  ".ProjNum) " & _
"GROUP BY tblOpPlanForecast.ProjNum, tblOpPlanForecast.DevCenter;"