StrSQL Error in Access VBA

Question

I have an Access VBA subroutine which is as follows:

  Sub SampleReadCurve()

    Dim rs As Recordset
    Dim iRow As Long, iField As Long
    Dim strSQL As String

    strSQL = "SELECT * FROM dbo_VolatilityInput5" & "ORDER BY MaturityDate"
    Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)



    If rs.RecordCount <> 0 Then

    Do While Not rs.EOF

        Dim BucketTermAmt As Long
        Dim BucketTermUnit As String
        Dim BucketDate As Date
        Dim MarkAsOfDate As Date
        Dim InterpRate As Double
        MarkAsOfDate = rs!MarkAsOfDate
        BucketTermAmt = 3
        BucketTermUnit = "m"
        BucketDate = DateAdd(BucketTermUnit, BucketTermAmt, MarkAsOfDate)
        InterpRate = CurveInterpolateRecordset(rs, BucketDate)
        Debug.Print BucketDate, InterpRate



   rs.MoveNext

Loop

   End If

End Sub

dbo_Volatility5 is a table in the Access database.

However, when I execute this code it gives me a runtime error "Syntax error in FROM clause" for the strSQL statement. As far I can tell the strSQL syntax is correct. What am I doing wrong?

[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) — Andre, Dec 17 '15 at 15:45

score 3 · Accepted Answer · answered Dec 17 '15 at 15:27

3

You're missing a space after dbo_VolatilityInput5. Quite a common mistake when concatenating multiple strings to form a SQL query.

I prefer to put each part on a new line:

strSQL = "SELECT * "
strSQL = strSQL & "FROM dbo_VolatilityInput5 "
strSQL = strSQL & "ORDER BY MaturityDate"

etc.

answered Dec 17 '15 at 15:27

Mark Butler

895
1
5
18

2

When I have a problem like this I use the debugger: set a break point on the "Set rs =" line, grab a copy of string in strSQL, and paste it into the query designer in Access. It's a great way to visualize the problem. – nicomp Dec 17 '15 at 15:30
Yes, or alternatively add a quick and dirty `Debug.Print strSQL` before the `Set rs = ...` – Mark Butler Dec 17 '15 at 15:31
Hey thank you for replying. I replaced it with each part on a new line as you showed, but I am still getting the same error. What else could be wrong? – beeba Dec 17 '15 at 15:32
Definitely. If it's a missing space that will usually be enough. Unless I've already stared at it for 3 hours. :) – nicomp Dec 17 '15 at 15:32
1

It was exactly the problem that you said with the missing space. However it didn't work until I restarted Access. – beeba Dec 18 '15 at 15:04
If I wanted to add a variable, to select where the variable CurveID = 15, what would be the correct syntax? Why does CurveID = 15 strSQL = "SELECT * FROM VolatilityOutput WHERE CurveID=" & CurveID & " ORDER BY MaturityDate" not work? – beeba Dec 24 '15 at 14:11
I'm presuming there is a line break after `CurveID = 15`...? (I know Stack removes line breaks from comments). Otherwise the syntax looks fine - unless the `CurveID` field is a string, in which case you'll need to use single quotes -- although it seems more likely to be a number field. Have you used something like `Dim CurveID as Integer`? – Mark Butler Dec 26 '15 at 18:16

score 1 · Answer 2 · answered Dec 17 '15 at 15:34

1

Just correct your code, that way:

strSQL = "SELECT * FROM dbo_VolatilityInput5 ORDER BY MaturityDate"

There is no need to concatenate your string, as it don't have any variable. If so, like a WHERE clause, do as follows:

strSQL = "SELECT * FROM dbo_VolatilityInput5 " & some_variable & " ORDER BY MaturityDate"

See that is a space after the quotes.

answered Dec 17 '15 at 15:34

Flávio Filho

475
4
14

hi. if I follow your second example, then to add a variable the syntax should be CurveID = 15 strSQL = "SELECT * FROM VolatilityOutput WHERE CurveID=" & CurveID & " ORDER BY MaturityDate" is this correct? – beeba Dec 24 '15 at 15:03

StrSQL Error in Access VBA

2 Answers2