Re: SQL syntax

Tech-Archive recommends: Fix windows errors by optimizing your registry


"Jason Keats" <jkeats@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:efqGXosyJHA.1712@xxxxxxxxxxxxxxxxxxxxxxx
Edward wrote:
Hello

I'm receiving a 'Too Few Perameters. expected 1' error when trying to use
a variable in place of a number in a Where statemet of an SQL string.

------ code
Private Sub cmdOutput_Click(Index As Integer)
Dim DB As Database, RS As DAO.Recordset
strSQL = "SELECT tblOutPoints.ID, tblOutPoints.Bit_No,
tblOutPoints.Bank_No " & _
"FROM tblOutPoints " & _
"WHERE tblOutPoints.ID = Index;"

' "WHERE tblOutPoints.ID = 2;"

Set DB = OpenDatabase(StoredDataPath)
Set RS = DB.OpenRecordset(strSQL)
MsgBox RS!Time_Msec

------ end code

The second commented Where using a numeral 2 works perfect. Access
creates a string as follows: WHERE (((tblOutPoints.ID)="Index"));
however it also fails.

Any suggestions appreciated.

PS: I'm looking for an Access-based SQL syntax checker/corrector that
one of the MVPs referred me to a few years back. May have been Douglas
Steele.

Replace

"WHERE tblOutPoints.ID = Index;"

with

"WHERE tblOutPoints.ID = " & Index & ";"

HTH

That caused a Compile Error: Expected end of statement highlighting the ";"
at the end.

"WHERE tblOutPoints.ID = &Index& ;" produced syntax error - missing
operator.


.

Relevant Pages

  • Re: SQL syntax
    ... I'm receiving a 'Too Few Perameters. ... expected 1' error when trying to use a variable in place of a number in a Where statemet of an SQL string. ... I'm looking for an Access-based SQL syntax checker/corrector that one of the MVPs referred me to a few years back. ...
    (microsoft.public.vb.database)
  • SQL syntax
    ... I'm receiving a 'Too Few Perameters. ... variable in place of a number in a Where statemet of an SQL string. ... I'm looking for an Access-based SQL syntax checker/corrector that one ...
    (microsoft.public.vb.database)
  • Re: SQL syntax
    ... I'm receiving a 'Too Few Perameters. ... expected 1' error when trying to use a variable in place of a number in a Where statemet of an SQL string. ... I'm looking for an Access-based SQL syntax checker/corrector that one of the MVPs referred me to a few years back. ...
    (microsoft.public.vb.database)
  • Recordsets, indexes, and null values
    ... All variables are defined per Option explicit, ... When I run the SQL statement in SQL view of an Access Query, ... I'm receiving results when I run the SQL outside VBA... ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL syntax
    ... use a variable in place of a number in a Where statemet of an SQL ... I'm looking for an Access-based SQL syntax checker/corrector ... properly format SQL strings? ...
    (microsoft.public.vb.database)