wildcard search using visual basic problem

[Davros]

first i must thank everyone here. this forum has proved invaluable for some of the simple databases i have created. now i have a problem which i cannot find the answer for.
i'm using access 2000
basically i am creating a cemetery database so most of the information i'm dealing with is duplicated. to create a unique record i have six primary keys on the one table. ie people with the same name, age, and birthday can be buried in the same grave.
anyway i need to do a wildcard seach from a form (QBF) based on surname and firstname. i have downloaded the code below and modified it:

Private Sub cmdsearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As querydef
Set dbNm = CurrentDb()

'Constant Select statement for the Query definition
strSQL = "SELECT searchtestdata.Surname; searchtestdata.Firstname; " & " FROM searchtestdata"

strWhere = "WHERE"

strOrder = "ORDER BY searchtestdata.autonumber;"

'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.txtsurname) Then '<--If the textbox txtFName contains no data THEN do nothing
strWhere = strWhere & " (searchtestdata.Surname) Like '*" & Me.txtsurname & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtfirstname) Then
strWhere = strWhere & " (Searchtestdata.firstname) Like '*" & Me.txtfirstname & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("quesearchtestdata")
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder

'Open the Query
DoCmd.OpenQuery "quesearchtestdata", acViewNormal

End Sub

the code stops at the line:

qryDef.SQL = strSQL & " " & strWhere & " " & strOrder

and tells me either that i have "run-time error 3142 - characters found at end of SQL statement" - this is the error i am recieving at the moment, or more usually " run-time error 3141 - which tells me the SELECT - WHERE statement is wrong.
i am definetly no expert in VB - i am only just starting using it.
so guys any idea?
thanks in advance

 

[GUIDO22]

When you truncate the WHERE string you are chopping of the apostrophe as well...
Change the code to remove 3 chars instead

ie.
'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 3)

 

[Davros]

hi
thanks for the reply
i have changed the - 5 to - 3 but it made no difference. i'm still getting the message

"run-time error 3142 - characters found at end of SQL statement"

thanks anyway

 

[GUIDO22]

Remove all semicolons from your SQL..... that should do it....!

 

[Davros]

hi
remove all semicolons and replace with what?? colons?

 

[WayneRyan]

D,

Instead of this:

qryDef.SQL = strSQL & " " & strWhere & " " & strOrder

Make a new textbox named txtDebug and put:

Me.txtDebug = strSQL & " " & strWhere & " " & strOrder

Then paste the contents of txtDebug here. Much easier to fix if you can
see it.

Wayne

 

[GUIDO22]

hi
remove all semicolons and replace with what?? colons?

With nothing! They are not needed - I have replicated this code on a sample DB I using your SQL. It did indeed error as you have detailed but having removed the semicolons the query ran correctly....

 

[WayneRyan]

Davros,

Replace the first semi-colon with a comma, replace the 2nd with nothing:

strSQL = "SELECT searchtestdata.Surname, searchtestdata.Firstname " & " FROM searchtestdata"

Also, if you view the entire string in the immediate window (or a textbox)
you can spot run-on things like:


FROM searchtestdataWhere <-- this will confuse the parser.

Wayne

 

[Davros]

guys
thanks very much for the replies. i'll try this in work tommorrow.
thanks again

 

[Davros]

guys - thank you very much
the code worked like a treat!
cheers