Results 1 to 4 of 4

Thread: Sql Statement

  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Exclamation Unanswered: Sql Statement

    Could somebody help with the below SQL string which doesn't work in the VBA procedure.


    strSQL = "SELECT SAL_1.[EMPLOYEE], SAL_1.[NAME_F_N], SAL_1.[AFF_REG], SAL_1.[FUNCTION], SAL_1.[GRADE], [TEST DATABASE].[Tests], CInt(Left([sal_1]![employee],3)) AS [Country code] " & _
    "FROM [TEST DATABASE] " & _
    "LEFTJOIN SAL_1 " & _
    "ON [TEST DATABASE].EMPLOYEE = SAL_1.ID " & _
    "WHERE [SAL_1].AFF_REG " & districtresult & " [SAL_1].FUNCTION " & functionresult & " [TEST DATABASE].[Tests] " & testresult & " [SAL_1].COMPANY " & companyresult & " AND [SAL_1].IN_OUT = '0';"


    access gives the follwoing error:
    Runtime error 3131
    Syntax error in the FROM clause.

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    I don't see any comarators(=,LIKE,etc.) or AND/OR statements in your predicate. Unless you have them built into your variables.

  3. #3
    Join Date
    Oct 2004
    Location
    London
    Posts
    21
    The problem with this statement is most likely to be the 'WHERE' clause.
    It is very hard to tell because it is not clear what values are held in the variables: districtresult, functionresult, testresult and companyresult.

    If these variables hold values similar to: " = 'aff_reg_value' and " then I think the statement will run correctly. So you may need to check the values being passed to these variables.

    If these variables hold values similar to: "aff_reg_value" then you need to change this string so that the " = '" and " and " seperarate each variable.

    For example:
    strSQL = "SELECT SAL_1.[EMPLOYEE], SAL_1.[NAME_F_N], SAL_1.[AFF_REG], SAL_1.[FUNCTION], SAL_1.[GRADE], [TEST DATABASE].[Tests], CInt(Left([sal_1]![employee],3)) AS [Country code] " & _
    "FROM [TEST DATABASE] " & _
    "LEFTJOIN SAL_1 " & _
    "ON [TEST DATABASE].EMPLOYEE = SAL_1.ID " & _
    "WHERE [SAL_1].AFF_REG = '" & districtresult & "' AND [SAL_1].FUNCTION = '" & functionresult & "' AND [TEST DATABASE].[Tests] = '" & testresult & "' AND [SAL_1].COMPANY = '" & companyresult & "' AND [SAL_1].IN_OUT = '0';"

    * Please note the single quotes should only be used if the variable is a string.

    Hope this helps.

  4. #4
    Join Date
    Oct 2004
    Posts
    7

    Arrow Sql Statement

    I manged to have the SQL working using the follwoing thanks for the suggestion of using AND:

    strSQL = "SELECT [SAL_1].EMPLOYEE, [SAL_1].NAME_F_N, [SAL_1].FUNCTION, [SAL_1].AFF_REG, [TEST DATABASE].* FROM [TEST DATABASE] LEFT JOIN SAL_1 ON [TEST DATABASE].Employee = SAL_1.ID " & _
    "WHERE [TEST DATABASE].[Tests] " & testresult & " AND [SAL_1].FUNCTION " & functionresult & " AND [SAL_1].AFF_REG " & districtresult & " AND [SAL_1].COMPANY " & companyresult & " AND CInt(Left([SAL_1]![EMPLOYEE],3)) " & localresult & " AND [SAL_1].[IN_OUT] = 0 ;"




    Now I am confronted with the follwoing SQL which access doesn't like when I put OR:

    strSQL = "SELECT DISTINCT [SAL_1].EMPLOYEE, [SAL_1].NAME_F_N, [SAL_1].FUNCTION, [SAL_1].AFF_REG, [TEST DATABASE].[tests] FROM [SAL_1] LEFT JOIN [TEST DATABASE] ON [SAL_1].ID = [TEST DATABASE].Employee " & _
    "GROUP BY [SAL_1].EMPLOYEE, SAL_1.NAME_F_N, SAL_1.FUNCTION, SAL_1.AFF_REG, [TEST DATABASE].Tests, SAL_1.COMPANY, CInt(Left([SAL_1]![EMPLOYEE],3)), SAL_1.IN_OUT " & _
    "HAVING [TEST DATABASE].[Tests] " & testresult & " AND [TEST DATABASE].[employee] " & employeresult & " AND [SAL_1].FUNCTION " & functionresult & " AND [SAL_1].AFF_REG " & districtresult & " AND [SAL_1].COMPANY " & companyresult & " AND CInt(Left([SAL_1]![EMPLOYEE],3)) " & localresult & " AND [SAL_1].[IN_OUT] = 0 " & _
    "OR [TEST DATABASE].[Tests] " & testniet & " AND [TEST DATABASE].[employee] " & employeniet & " AND [SAL_1].FUNCTION " & functionresult & " AND [SAL_1].AFF_REG " & districtresult & " AND [SAL_1].COMPANY " & companyresult & " AND CInt(Left([SAL_1]![EMPLOYEE],3)) " & localresult & " AND [SAL_1].[IN_OUT] = 0 ;"

    I want that the query looks for records based on certain variables or other depednant variables. For example if Test = 3 then Id = Like'*' and if Test = Like '*' then id = Like'*'

    In the baove statement the 2 dependant variables are testresult with testniet and employeresult with employeniet.

    Thanks for any suggestion.

Posting Permissions

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