CODEWITHSUNDEEP
sqlvbams-access
Capnbigal
Capnbigal

Reputation: 27

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

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?

Upvotes: 0

Views: 92

Answers (3)

iDevlop
iDevlop

Reputation: 25252

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)

Upvotes: 1

hc_dev
hc_dev

Reputation: 9377

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.

Upvotes: 0

I'm Not A Robot
I'm Not A Robot

Reputation: 294

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.

Upvotes: 1

Related Questions