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)
smm6809Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try this:

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

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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 Access MVP)Database ArchitectCommented:
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)President / OwnerCommented:
<<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 Access MVP)Database ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
And I think I ... need more coffee :-)

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.