Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2012
    Posts
    18

    Unanswered: I Keep Getting These Errors

    I have this SQL code that I am trying to run in my VBA code however i keep getting the following errors when i try to compile. Expected: Line number or label or statement or end of statement and the other error that I am getting is Variable not defined. Here is my code below

    strSQL = " SELECT FirmNumber, NumberofAccounts, EmployeeID, ErrorCodeDescription, "
    ErrorCodesandCorrections , Agreements, TypeofCommunication, Time, ""
    [Opened Date]
    FROM TrackingSystem3
    WHERE NumberofAccounts = " SELECT Max (NumberofAccounts) from TrackingSystem3; " And Time = " SELECT Max (Time) from TrackingSystem3; "

    Please help if you can...Thanking you in advance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the string concatenation (read joining together) character in Access is &
    to mergre accross lines you need th _ as well
    so your SQL shoudl probablky look similar to this
    Code:
    strSQL = " SELECT FirmNumber, NumberofAccounts, EmployeeID, ErrorCodeDescription, " & _
    "ErrorCodesandCorrections , Agreements, TypeofCommunication, Time, [Opened Date] FROM TrackingSystem3" & _
    "WHERE NumberofAccounts = SELECT Max (NumberofAccounts) from TrackingSystem3;  And Time = SELECT Max (Time) from TrackingSystem3; "
    OR
    Code:
    strSQL = " SELECT FirmNumber, NumberofAccounts, EmployeeID, ErrorCodeDescription, " 
    strSQL = strSQL & "ErrorCodesandCorrections , Agreements, TypeofCommunication, Time, [Opened Date] FROM TrackingSystem3 " 
    strSQL = strSQL & "WHERE NumberofAccounts = SELECT Max (NumberofAccounts) from TrackingSystem3;  And Time = SELECT Max (Time) from TrackingSystem3; "
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2012
    Posts
    18

    I Keep Getting These Errors

    Dear Healdem

    Thanks for such a quick response howwever its still appears that its not working correctly. The only way i can get the code to run is to double click on TrackingSystem3 Query while i am testing and then it works. However i want this done automatically so the user wont have to do this. I will attache a copy of my code and see if you can tell me why its not working? Thanking You in advance.
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2012
    Posts
    18
    Dear Healdem

    I guess it wouldn't work if i didnt add

    DoCmd****nSQL strSQL

    Now let's see what happens.

  5. #5
    Join Date
    Jul 2012
    Posts
    18
    I am getting a RunTime error Syntax error in from clause 3131

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so can we see the SQL that generates that error?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2012
    Posts
    18
    Here is the code that's not working I am wondering if I am adding the information in the correct location?

    'Testing Code
    strSQL = " SELECT FirmNumber, NumberofAccounts, EmployeeID, ErrorCodeDescription, " & _
    " ErrorCodesandCorrections , Agreements, TypeofCommunication, Time, [Opened Date] FROM TrackingSystem3" & _
    " WHERE NumberofAccounts = SELECT Max (NumberofAccounts) from TrackingSystem3; And Time = SELECT Max (Time) from TrackingSystem3; "

    DoCmd****nSQL strSQL

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you will find the problem is your sub selects
    Access/JET uses its own dialect
    Microsoft Access tips: Subquery basics
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Reabernathy View Post
    Here is the code that's not working I am wondering if I am adding the information in the correct location?

    'Testing Code
    strSQL = " SELECT FirmNumber, NumberofAccounts, EmployeeID, ErrorCodeDescription, " & _
    " ErrorCodesandCorrections , Agreements, TypeofCommunication, Time, [Opened Date] FROM TrackingSystem3" & _
    " WHERE NumberofAccounts = SELECT Max (NumberofAccounts) from TrackingSystem3; And Time = SELECT Max (Time) from TrackingSystem3; "
    SQL is not my strong suit, but 3131 is an error in the From clause, and you've got a semi-colons when you're only supposed, I believe, to have one, at the very end of your statement, or is that what Heladem is referring to?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Oct 2006
    Posts
    110
    If I can chime in.. Try using the parentheses to enclose your subqueries.

    I was able to create a quick table and query and got this working.

    Change to:
    'Testing Code
    strSQL = " SELECT FirmNumber, NumberofAccounts, EmployeeID, ErrorCodeDescription, " & _
    " ErrorCodesandCorrections , Agreements, TypeofCommunication, Time, [Opened Date] FROM TrackingSystem3" & _
    " WHERE NumberofAccounts = (SELECT Max (NumberofAccounts) from TrackingSystem3) And Time = (SELECT Max (Time) from TrackingSystem3)"

  11. #11
    Join Date
    Jul 2012
    Posts
    18
    I am getting a Run-Time Error 3131
    Syntax Error is From clause
    Here is the code that I am trying to run and i cant figure this out.......PLEASE Help...

    Dim strSQL As String

    strSQL = " SELECT FirmNumber, NumberofAccounts, EmployeeID, ErrorCodeDescription, " & _
    " ErrorCodesandCorrections , Agreements, TypeofCommunication, Time, OpenedDate FROM TrackingSystem3 " & _
    " INTO TempReport_Table from TrackingSystem3 " & _
    " WHERE NumberofAccounts = (SELECT Max (NumberofAccounts) from TrackingSystem3) And Time = (SELECT Max (Time) from TrackingSystem3); "

    DoCmd****nSQL strSQL

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    TIME is a reserved word.
    suggest you break you SQL into segments and try to work out whuihc part trips up the SQL engine
    can we see the actual SQL sent to the parser (IE the value of strSQL as opposed to the code that creates the SQL)
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Oct 2006
    Posts
    110
    Healdem is correct. Time can't be used as a field name. Change your field names and try again. Not sure what you were trying to do in your last code post, but if you are trying to insert rows into the TempReport_Table, this is what you need.


    Dim strSQL As String
    strSQL = "INSERT INTO TEMPREPORT_TABLE (FirmNumber, NumberofAccounts, EmployeeID, ErrorCodeDescription,ErrorCodesandCorrections, Agreements, TypeofCommunication, Time2, OpenedDate) " & _
    "SELECT FirmNumber, NumberofAccounts, EmployeeID, ErrorCodeDescription, " & _
    " ErrorCodesandCorrections, Agreements, TypeofCommunication, Time2, OpenedDate FROM TrackingSystem3 " & _
    " WHERE NumberofAccounts = (SELECT Max (NumberofAccounts) from TrackingSystem3)And Time2 = (SELECT Max (Time2) from TrackingSystem3) "
    DoCmd****nSQL strSQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •