SQL syntax problem 1

[msay]

Can anyone tell me why this statement does not work?

SQL = "SELECT * FROM reservations WHERE rsdate = #" & A & "# " & " and name =" & Chr(34) & B & Chr(34) And plane = " & Chr(34) & C & Chr(34)"""

 

[hellferret]

The quotes have all gone walkabout.

SQL = "SELECT * FROM reservations WHERE rsdate = #" & A & "# " & " and name =" & Chr(34) & B & Chr(34)& " and plane = " & Chr(34) & C & Chr(34)


Apart from that it looks OK. But you never know.

If it doesn't work then try it with example values in Query Analyzer. Then try it while missing out one of the WHERE clauses as this is where the problem is most likely to lie.
HF

 

[chiph]

msay -

I try and format my SQL for readability. It usually points out problems like this. Plus it makes it easier to maintain 6 months from now.
[tt]
Dim sSQL as string

sSQL = ""
sSQL = sSQL & " SELECT *"
sSQL = sSQL & " FROM reservations"
sSQL = sSQL & " WHERE rsdate=" & A
sSQL = sSQL & " AND name='" & B & "'"
sSQL = sSQL & " AND plane='" & C & "'"
[/tt]
Note that I've replaced your double-quotes with ANSI SQL standard single-quotes. I've also removed the pound-symbols around your date value, as that's also not ANSI standard (typically used with MS-Access).

Something you may want to consider is replacing the concatenation of variables with ADO command-object parameters. It would look something like this:

[tt]
Dim adoComm as ADODB.Command
Dim adoRS as ADODB.Recordset
Dim sSQL as string

sSQL = ""
sSQL = sSQL & " SELECT NumPassengers,"
sSQL = sSQL & " Fare,"
sSQL = sSQL & " DepartAirportCode"
sSQL = sSQL & " FROM reservations"
sSQL = sSQL & " WHERE rsdate=?"
sSQL = sSQL & " AND name=?"
sSQL = sSQL & " AND plane=?"

Set adoComm = New ADODB.Command
Set adoRS = New ADODB.Recordset

adoComm.ActiveConnection = adoConn [tab]'Use connection object
adoComm.CommandText = sSQL

adoComm.Parameters.Append adoComm.CreateParameter("rsdate", adDate, adParamInput, , A)
adoComm.Parameters.Append adoComm.CreateParameter("name", adVarChar, adParamInput, , B)
adoComm.Parameters.Append adoComm.CreateParameter("plane", adVarChar, adParamInput, , C)

adoRS = adoComm.Execute
If Not (adoRS.BOF And adoRS.EOF) Then
[tab]' Code to loop through recordset
End If
[/tt]

Using the adoCommand object has the benefit of replacing any single or double-quotes in your string variables so they'll show up correctly in the database. They also execute faster.

I also changed your SELECT clause to spell out the column names. Looks like a pain to type all that in, and it is . But it executes faster since the database engine doesn't need to query the system catalog to find out what columns are in the table.

Hope this helps.

Chip H.

 

[msay]

Thank you! I'm all set. I appreciate all the help!!!