Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Syntax error in SQL string

Can somebody please tell me what's wrong below? I keep getting "Syntax error in FROM clause". Maybe I'm tired or something, but I just don't see it. I am reading an access database.

   
 OpenDB mConn, DBPath

    Set rs = New ADODB.Recordset
   
    strPlanValue = "CL"
    strSQL = " SELECT qRetChkReq.*  " _
             & "FROM qRetChkReq " _
             & "WHERE qRetChkReq.[strPlanCD]= '" & strPlanValue & "';"


    Call rs.Open(strSQL, mConn, adOpenKeyset, adLockOptimistic, adCmdTable)
0
smm6809
Asked:
smm6809
  • 6
  • 6
  • 2
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try this:

             & "FROM qRetChkReq " _
             & "WHERE qRetChkReq.[strPlanCD]= " & Chr(34)  & strPlanValue & Chr(34)

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Added Space in front of FROM also

    strPlanValue = "CL"
    strSQL = " SELECT qRetChkReq.*  " _
            & " FROM qRetChkReq " _
             & "WHERE qRetChkReq.[strPlanCD]= " & Chr(34)  & strPlanValue & Chr(34)
0
 
smm6809Author Commented:
Nope, I don't understand, it should be working.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
FWIW, when looking for errors like that, put a breakpoint here:

Call rs.Open(strSQL, mConn, adOpenKeyset, adLockOptimistic, adCmdTable)


  Then call up the debug window (Ctrl/G) and do:

 ? strSQL

 and hit return.  Usually easy then to spot syntax errors like this.

Jim.
0
 
mbizupCommented:
Your line continuation characters look okay, but just in case:


    strSQL = "SELECT qRetChkReq.* FROM qRetChkReq WHERE qRetChkReq.[strPlanCD]= '" & strPlanValue & "'"
0
 
smm6809Author Commented:
Been doing that all along.
SELECT qRetChkReq.*  FROM qRetChkReq WHERE qRetChkReq.strPlanCD= 'CL';

When I plug it into access it works fine.
It's almost like I don't have references I need or something.
0
 
smm6809Author Commented:
I am running this code in excel, but I didnt think it would make a difference.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Is this strPlanCD  ... the actual field Name ?  Looks like a variable Name ?

mx
0
 
smm6809Author Commented:
Variable from code snippet:
 strPlanValue = "CL"
0
 
smm6809Author Commented:
Here is the complete code:

Public Sub RetChkReq()
'On error goto ErrHandler
Dim strPlanCD As String
Dim strPlanValue As String

    ysnValid = True
    GetDBPath
   
    If ysnValid = False Then
        MsgBox "Not a valid database path, please use only valid database paths.", vbInformation, "Database Path Error"
        Exit Sub
    End If
   
    Worksheets("Retirees VPS").Activate
   
    OpenDB mConn, DBPath

    Set rs = New ADODB.Recordset
   
    strPlanValue = "CL"
    strSQL = " SELECT qRetChkReq.*" _
             & "FROM qRetChkReq " _
             & "WHERE qRetChkReq.[strPlanCD]= " & Chr(34) & strPlanValue & Chr(34)



    Call rs.Open(strSQL, mConn, adOpenKeyset, adLockOptimistic, adCmdTable)
    With rs
        GrandTotal = 0
        .MoveFirst
        Do While .EOF = False
                strPlanCD = Trim(rs!strPlanCD)
                GrandTotal = GrandTotal + rs!numTotal
            .MoveNext
        Loop
            '.MoveLast
                Find_Range strPlanCD, Cells
                RemoveFromString FirstAddress
                'put code here to find the dollar amount in that row
                strCell = strLCell & strRCell
                Range(strCell).Offset(0, 6) = GrandTotal
                GrandTotal = 0
    End With
   
        CloseDB mConn
    Exit Sub
   

ErrHandler:

End Sub
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Been doing that all along.

SELECT qRetChkReq.*  FROM qRetChkReq WHERE qRetChkReq.strPlanCD= 'CL';>>

 And this is what you have in the debug Window when you break on the rs.open?

Jim.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Dim strPlanCD As String

You are using that here

"WHERE qRetChkReq.[strPlanCD]= " & Chr(34) & strPlanValue & Chr(34)

but the first part of the WHERE clause s/b a Field Name from qRetChkReq ...



mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well ... I see you have this:

Trim(rs!strPlanCD)

so ... I guess strPlanCD is also a Field name ?

mx
0
 
smm6809Author Commented:
I didn't realize I was using the same name as a variable in the code. Like I said, must be tired.  Thank You!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And I think I ... need more coffee :-)

mx
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now