ms-access run-time error 3075 extra ) in vbasql-CodePudding

Code

strSQL = "SELECT tblHS_area_fields.hsaf_id " _
& "FROM tblHS_area_fields " _
& "WHERE (((tblHS_area_fields.hs_area_id)=" & hs_area_id & ") AND ((tblHS_area_fields.hsf_id)=13))"


Set rs = db.OpenRecordset(strSQL)

Errors

The error when trying to run from a Form is:

Extra ) in query expression '(((tblHS_area_fields.hs_area_id)=" &
> hs_area_id & ") AND ((tblHS_area_fields.hsf_id)=13))'

Getting an error from immediate window:

Compile error: expected:  line number or label or statement or end of statement

All fields are numbers.

What is wrong with the VBA code and SQL statement?

CodePudding user response：

Just remove all brackets in your sql:

strSQL = "SELECT tblHS_area_fields.hsaf_id " & _
"FROM tblHS_area_fields " & _
"WHERE tblHS_area_fields.hs_area_id = " & hs_area_id & " AND tblHS_area_fields.hsf_id = 13 "

In this case you don't need the brackets.

CodePudding user response：

Consider to debug-print and log your SQL before executing: Debug.print(strSQL).

Opening brackets must match closing ones

Some break-down helps recognizing and matching, correct is:

strSQL = "SELECT hsaf_id" _
& " FROM tblHS_area_fields" _
& " WHERE (" _
  & "( hs_area_id =" & hs_area_id & ")" _
  & " AND ( hsf_id = 13 )" _
& ")"

For SQL templates you could also use string replace function or string-templating with a custom-function. See VBA string interpolation syntax.

CodePudding user response：

Not an answer, but too long for a comment:
Next time, add a Debug.Print strSql, then create a query in SQL view, copy you SQL statement from the debug window (ctrl G) and paste you statement there.
Or just paste it in Visual Studio...
You should then quickly see the issue(s)