designspeaks designspeaks - 10 months ago 63
SQL Question

SQL query returning Too Few Parameters

I have an access database that I am attempting to reference a SQL query

Set dba = CurrentDb

SQL = "SELECT * FROM tbl_NewHireStep WHERE Location = '" & Site & "' AND E-Verified = TRUE "

Set rst = dba.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)

Keeps giving me a Too few parameters error. In SQL it works great. What am I doing wrong?

Answer Source

The hyphen, -, is an Access reserved symbol. If you want to use it in a field or table name, then you must always put that field or table name inside square brackets when you reference it. For example,

SQL = " SELECT * FROM tbl_NewHireStep " & _
      " WHERE Location = '" & Site & "' AND [E-Verified] = TRUE "

It's far better to avoid using reserved words and symbols in the first place. Several years ago, Allen Browne compiled a list of Problem names and reserved words in Access. I'm not sure how current the list is, but it is a great reference when creating new database schemas.