Searching for records that match user input

[TheKidLarose]

I am a newbie at SQL. I created a search form with a text box called bxSearch and 2 fields named LastName and FirstName respectively. These 2 fields contain over a 1000 records of customer names.

Is there code that I can use so that when a user enters a letter such as "S" all the last names beginning with this letter. I have tried several ways but have failed to date.

I definetely need help

tks

Denis

 

[Darrylles]

Hi,

You probably have a 'Search' button to allow user to start the search after entering their search character string.

In the <On Click> event of the button use this....

dim SqlStr as string

SqlStr = "SELECT * from mytable where Lastname LIKE " & me!txtLastname

formname.controlsource = SqlStr
formname.requery

You'll have to replace mytable, txtlastname and formname with your object names.

Regards,

Darrylle

&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk

 

[Darrylles]

Hi again,

Sorry, you need :

SqlStr = "SELECT * from mytable where Lastname LIKE '" & me!txtLastname & "*'"

Regards,

Darrylle &quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk

 

[TheKidLarose]

Hi Darrylle

Thanks for your reply. I must be doing something wrong because when I run the code the string passed on to the variable SqlStr is the entire line &quot;Select * from People Where LastName Like '&quot; & Me!bxSearch & &quot;*'&quot;

The query my from frmSearchGuest is based on is qrySearchGuest which is made up of 3 fields, LastName, FirstName and ID. This query is based on a table named People. The form I created for searching is frmSearchGuest which has a text field named bxSearch, a go button named btnSearch and an update button to update the information queried to a form named frmGuests. It also has 2 fields, LastName and FirstName which shows the name of all my guests

What am I doing wrong. Is the line of code (SQL) written ok with the appropriate names. Why is the entire statement passed on to the variable SqlStr

Thanks

Denis

 

[lespaul]

If nothing else there should be another *

SqlStr = &quot;SELECT * from mytable where Lastname LIKE '*&quot; & me!txtLastname & &quot;*'&quot;




Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[TheKidLarose]

Thanks Leslie and Darrylle

It still doe not work. I am passing on #NAME to the LastName field on my form. Maybe I do not know the proper syntax for using SQL in my VB Code. If you look at my second response all the fields and the information is there. If I was to use the fields in my second posting, how would I properly include the SQL statement in my code

I appreciate your patience

Denis

 

[lespaul]

Why don't you post your VB Script so we can see if the error is in there somewhere? Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!