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.

I don't see any comarators(=,LIKE,etc.) or AND/OR statements in your predicate. Unless you have them built into your variables.

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.

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.