Re: SQL syntax

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

Edward wrote:
"argusy" <argusy@xxxxxxxxxxxxxxx> wrote in message news:0018e942$0$9679$c3e8da3@xxxxxxxxxxxxxxxxxxxx
Edward wrote:
"argusy" <argusy@xxxxxxxxxxxxxxx> wrote in message news:0017c2f8$0$9732$c3e8da3@xxxxxxxxxxxxxxxxxxxx
Edward wrote:
"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.


just drop the ";"
and see if the following works
"WHERE tblOutPoints.ID = " & Index

I don't remember putting a ";" at the end of an SQL statement requiring a number, I just left it like I've shown you.

Now I'm curious. Gotta check that out.

Argusy
Wow! Thank you so much. That solved it..

BTW have you ever seen a few years old Access app that is used to properly format SQL strings?
It is a simple paste-here text box, and results text box.
No, I haven't played with Access programming very much, only via VB.
Try asking that in an acess ng

Graham

If I need to emply multiple WHERE criteria, how would it change the syntax?

I've tested:
"WHERE tblOutPoints.ID = " & Index AND tblOutPoints.Bit_No = " & Bit
and
"WHERE tblOpenEvents.Bit_No = " & Bit And "tblOpenEvents.Bank_No = " & Bank
Neither worked.



"WHERE tblOpenEvents.Bit_No = " & Bit & " And tblOpenEvents.Bank_No = " & Bank

Graham

.

Relevant Pages

  • Re: SQL syntax
    ... Edward wrote: ... 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. ...
    (microsoft.public.vb.database)
  • Re: binding combobox
    ... "Claes Bergefall" wrote: ... >> Can anyone tell me why I'm getting an error when my sql string is changed ... Please have a shot at it! ...
    (microsoft.public.dotnet.framework.windowsforms)