ADO Help....

[Harrild]

ok hello
this is my code for a search in a prog i am creating but i keep getting a syntax error with the FROM clause..
help


private sub cmdsearch_Click()
Dim search As String
Dim band As String
Dim album As String
Dim genre As String
Dim cdtype As String
Dim released As String
Dim company As String
Dim serial As String

On Error GoTo damn

band = Trim(txtband.Text)
album = Trim(txtalbum.Text)
genre = Trim(txtgenre.Text)
cdtype = Trim(txttype.Text)
released = Trim(txtreleased.Text)
company = Trim(txtcompany.Text)
serial = Trim(txtserial.Text)

search = "select * from cd_serial_numbers"
'search = search & "from cd_serial_numbers"

If Len(band) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & "where (band_name ='" & txtband.Text & "')"

End If

If Len(album) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & "where (album_name ='" & txtalbum.Text & "')"

End If

If Len(genre) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & "where (genre ='" & txtgenre.Text & "')"

End If

If Len(cdtype) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & "where (single/album ='" & txttype.Text & "')"

End If

If Len(released) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & "where (released ='" & txtreleased.Text & "')"

End If
If Len(company) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & " where (company ='" & txtcompany.Text & "')"

End If

If Len(serial) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & "where (serial ='" & txtserial.Text & "')"

End If


Debug.Print search
Adodc1.RecordSource = search
Adodc1.Refresh

damn:
MsgBox ("Something has gone wrong (duh)!")

End Sub

[Gen-X]

You dont have any spaces!!!!

Search = "Select * from cd_serial_numbers"
Where = "where number = 1"

MsgBox Search & Where

"Select * from cd_serial_numberswhere number = 1"

When you concatonate strings you must be sure there are enough spaces between each of your clauses... Without the space before the "where" when you add it to the end it goes straight on.

Simply print your SQL Query and it should be very obvious

[Harrild]

ok yep fixed the spaces but what is this
where = "where number = 1" business?

[Gen-X]

Its just an example because I couldn't be bothered trying to find the names of your tables and fields

[Harrild]

ok thanks....Gen-X