Too few parameters - run time error in recordset

[Lavey]

Hi guys,

I have a piece of code that opens a DAO connection to another database and retreives a recordset - but i get a runtime error 3061 - too few parameters (1). My SQL is not too good so am looking for some hints...

Dim DBS As Database
Dim rst As Recordset

Set DBS = OpenDatabase("D:\Chris\Access\Development\Backups\Airline Contacts.mdb&quot
Set rst = DBS.OpenRecordset("SELECT * FROM PlTest WHERE IATACode = " & Desi, dbOpenSnapshot)
If rst("IataCode&quot = Desi Then
...rest of my code...

If I delete the WHERE part of my SQL then I get a full recordset based on the PlTest Query (900 records).
Any ideas on how I make the recordset = the current 'Desi'.

Cheers......

 

[Databaseguy]

Yeah, shoving a SQL string into a recordset can be trying. At first glance, I noticed you've got to concoctenate for your variable:

Set DBS = OpenDatabase("D:\Chris\Access\Development\Backups\Airline Contacts.mdb&quot

Set rst = DBS.OpenRecordset("SELECT * FROM PlTest WHERE IATACode = " & Desi & ", dbOpenSnapshot)"
If rst("IataCode&quot = Desi Then
...rest of my code...
Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[Lavey]

DBguy,

If I concotenate the desi then this gives me a sytax error,
'iatacode = AA , dbopensnapshot'
Desi - is a bound control of the form that this code sits on.
I could move through the recordset until i hit as corresponding 'Desi' - but this is not very effeicient.

 

[Skeptik]

Set rst = DBS.OpenRecordset("SELECT * FROM PlTest WHERE IATACode = " & "Desi", dbOpenSnapshot)

or try this

Set rst = DBS.OpenRecordset("SELECT * FROM PlTest WHERE IATACode = '" & "Desi" & "'", dbOpenSnapshot)

 

[Lavey]

Skeptic,

That's it - I used the second statement without the " around the desi but with the ' either side of it!

"SELECT * FROM PlTest WHERE IATACode = '" & Desi & "'"

How darn annoying - but it finally works!!

Thank you both for your time !!

 

[raskew]

If Desi were a number, your original statement would have worked. However, it's apparently text, thus the necessary = '" & Desi "'". Were it a date, the statement would read ....= #" & Desi & "#"