basic SELECT, LIKE statement 2

[mrmovie]

Please forgive me, this is all new stuff for me.

Trying to build a simple 'query' form.
the idea being it pulls back a recordset of all records which match the criteria entered into a number of text boxes on a form.
I want to return a RecordSet which will prob then be displayed to the user so they can select/open/display/print the details of the returned matching records.

im stuck right at the start

Set rstResults = dbsDataBase.OpenRecordset(SELECT * FROM [Table A] WHERE FieldA LIKE " & Me.txtASPReference)

It says 'Syntax Error missing operator in query expression 'ASPRef LIKE myValue'

 

[mrmovie]

ok, ive put in " before the Me.txtASPReference and after it.
not getting the error anymore but now i dont get any record set returned

 

[Golom]

You're missing a quote sign and you need single quotes around the variable. You also need a wildcard character if you're using LIKE.

Set rstResults = dbsDataBase.OpenRecordset([COLOR=red][b]"[/b][/color]SELECT * FROM [Table A] WHERE FieldA LIKE [COLOR=red][b]'[/b][/color]" & Me.txtASPReference[COLOR=red][b] & "*'"[/b][/color])

 

[mrmovie]

if i do

("SELECT * FROM [ASP Line Data])
it only returns one record when i pipe oRs.RecordCount to the screen....

 

[Golom]

'" & Me.txtASPReference & "*'" - Starts with Me.txtASPReference

'*" & Me.txtASPReference & "'" - Ends with Me.txtASPReference

'*" & Me.txtASPReference & "*'" - Contains Me.txtASPReference

 

[mrmovie]

thanks for the feedback Golom. i have tried your suggestion and i no longer get the syntax error, however i am still not getting a record returned.

I am searching on 52TJY7
and i knwo i have a record with a value of

1-52TJY7 1-52TK4V 1

 

[Golom]

You need the * ... * version because the string you're looking for is inside the string you're searching (i.e. its not at the beginning or the end.)

 

[mrmovie]

off home now, thanks again Golom

 

[lespaul]

and is your query set up like:


'*" & Me.txtASPReference & "*'"

with the * around the front & back of your search string?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[mrmovie]

thanks Leslie, i seem to have the sql statement licked now.
was struggling with .Name and .Value but ok now.
one thing has vex'd me for a while is that RecordCount always returns 1, regardless of the num rows in the recordset, stupid thing

 

[lespaul]

I think you have to move to the end of the recordset in order to get the correct record count.

HTH

Leslie