Image of Navigational Panel mapped to Contents / Home / Search FindFirst
Tech Support Question
Image of Line Break

Q. I am a summer intern trying to create an application with VB. I would like to use the FindFirst method to find a record using a the value of a textbox on the form. Essentially the user inputs his name in the textbox, and click the Search button, and I'd like to invoke the FindFirst method using the user's name. What is the proper syntax, if this can be done?

Thanks for your time and help.

From: Pavan Desai
Email: pdesai@orbit.hr.att.com

A. What you want to do is not particularly difficult. Let's assume that your textbox is called txtName and your recordset is called dsLookup. 

  dsLookup.FindFirst "UserName Like """ & txtName.Text & "*"""

A couple of notes:

  1. I have used two double quotes to delimit the string within the string. Remember that we are passing SQL a string within which we need to delimit a string. I use two double quotes for this because VB resolves it to be one double quote when it resolves the string which is an appropriate delimiter. It also allows you to enter names like "O'Reilkly" without anyone getting confused.
  2. I used the Like operator so that we can search for partial matches. If you don't remember whether it's "Robertson" or "Robinson", just type in "Rob" and it will find the first match that starts with "Rob". This also requires the asterisk at the end of the string to indicate 'zero or more additional characters'. If you want to do exact matches just use = and don't append the asterisk.

I hope that explains what you need to do to get your search happening. Enjoy.

Q. Thanks for the detailed help on using FindFirst. Following your suggestion, I was able to execute the search query.

When I asked for your help, I had the following code for queries: 

  Set Rst = Db.OpenRecordset("Select * FROM Titles WHERE Title = '" & Text1.Text & "'", dbOpenSnapshot)

While this is adequate, it cannot resolve the apostrophes as in O'Reilly, and as you mentioned, using the two double quotes in Findfirst certainly solved that problem!!

Again, thanks so much for your help and time.

P.S. - Is there anyway to use the above SQL query to resolve the apostrophes also??

From: Pavan Desai
Email: pdesai@orbit.hr.att.com

A. Yep, you use the same technique. Your line of code here could be mended to: 

  Set Rst = Db.OpenRecordset("SELECT * FROM Titles WHERE Title = """ & Text1.Text & """", dbOpenSnapshot)

As you can see (if you look closely) I have just replaced your use of an apostrophe with two double quotes. It's really that simple. You can use the same technique anywhere, it's just part of the way VB resolves string literals.

I'm glad my previous answer helped you out. It's the precise and well described questions that are easiest to answer. Remember, enjoy your database.

Q. I have a doubt about FindFirst. I use the Brazilian kind of date that is:

day/month/year

When I use this: 

  dim data as date
  Set db =3D OpenDatabase(App.Path & "\programa.mdb")
  Set rst =3D db.OpenRecordset("PROGRAMA=C7=C3O", dbOpenDynaset)
  data =3D "7/1/96"
  rst.findfirst "data =3D#"& data & "#"

....I have a no match answer.

But if I look for a date, that has two digits for days, it works: 

  data =3D "17/1/96"
  rst.findfirst "data =3D#"& data & "#"

...then I have a found OK.

I tried all the possibilities, but there's always the same answer. When I use one digit (7)/1/96 doesn't work; when I use two digit (17)/1/96 it works. I wonder if you could help me?

Thank you,

From: Ricardo
Email: rhprod@netalpha.com.br

A. Nice to hear from you again. I think I know what your problem is as we use d/m/y dates in Australia too. Unfortunately Access always uses m/d/y.

What this means is that if it can resolve your date to m/d/y it will do so. If the first number is obviously a day however (being more than 12), it figures out that it is a day and responds appropriately.

The best thing I can recommend is that you always pass dates to JET using m/d/y format. I know it's a pain but it will always work without relying on JET to decide what is going on.

Image of Arrow to Previous Article Image of Arrow to Next Article

[TECH SUPPORT TOC]
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]