0
Set db = CurrentDb
Set qdf = db.CreateQueryDef("Unique_Address1_qry", strSQL)
Application.RefreshDatabaseWindow

strSQL = "SELECT field1 AS field1, field2 as field2 " & vbCrLf & _
"'" & strScenario & "'" & "AS Scenario, " & vbCrLf & _
"FROM table_tbl " & vbCrLf & _
"GROUP BY field1,....; "

The strScenario contains a calculation formula like "[field2]/2+3" the result is that in every record for the calulated fied, "[field2]/2+3" is showing insted of the calcultion.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Dirk
  • 1
  • Remove the single quotes `'` around the variable? the `'` is telling the query that it is a literal text string to return. – Scott Craner Sep 09 '16 at 13:17

2 Answers2

2

I'm not going to repeat what @Comintern said, but I can give you a fishing rod:

  • Place a breakpoint (F9) on the line immediately following the strSQL = assignment.
  • Run the code.

  • When execution reaches the breakpoint, press Ctrl+G to bring up the immediate pane, and type:

    ?strSQL

This will output the string value (? is shorthand for Print), which you can then inspect yourself, copy to the clipboard, paste to some new query and try to execute.

Then you can fix the code, move the current line back to the modified strSQL = assignment, try ?strSQL again, rinse & repeat until your code generates a valid SQL query - then you can remove the breakpoint and hit F5 to resume execution, and you have working code.

Give a man a fish, he'll eat for a day. Give a man a fishing rod, he'll eat for the rest of his life.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • 1
    So true. Links for this: [How to debug VBA code](http://www.cpearson.com/excel/DebuggingVBA.aspx) and [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) – Andre Sep 09 '16 at 14:12
1

The query really should be parameterized...but you have strScenario quoted as a literal. You're also missing a comma after "field2" and a space after strScenario.

It should look more like this:

strSQL = "SELECT field1 AS field1, field2 as field2, " & _
    strScenario & " AS Scenario FROM table_tbl GROUP BY field1,....; "

Note that you can see the result of the SQL you build by simply putting Debug.Print strSQL after your assignment and looking at it in the Immediate window.

Comintern
  • 21,855
  • 5
  • 33
  • 80
  • when I use a simple formula it works, but when I start to using things like : IIF([SumOfVolume]>=5;[SumOfLDM]*1750;0) this gives problems. Is the IIF function that create the isse. – Dirk Sep 09 '16 at 16:34
  • 1
    @Dirk - Look at the result in the immediate window. Is it valid SQL for MS Access? In this case, it isn't - `;` terminates a statement - they should be `,`. If in doubt, copy it into the Access SQL designer and try to execute it. Access will let you know where your syntax error are. – Comintern Sep 09 '16 at 16:43
  • I changed the ; with , for the IIF statement and 1,2 into 1.2 and it works. Thx a lot for the help. :-) – Dirk Sep 12 '16 at 08:03