Access 2002 Unbound List Search

[High_Jumbllama]

I am working on doing a simple search. In my test(testing whether or not I know what I am doing) access form, I set the row source as

SELECT Results.ID, Results.fname, Results.lname, Results.address, Results.city, Results.state, Results.zip, Results.phone
FROM Results
WHERE (((Results.fname)=[Forms]![Search]![Text2]));

I have a requery button that causes the target unbound list box to refresh according to what is in the search box but it displays nothing when nothing is in the search box.

How would I cause it to display everything when nothing is in the search box?

[schmosef]

Wow, it's been a long time since i've done access programming. I was so glad when I migrated to SQL server.

For what you're doing there are a few work arounds. You could use flow control (that is, an "if...then...else block") in the click event of your "requery" button. Use that opportunity to set the appropraite row source expression according to whether you have something to search for.

There are other ways but they are more complicated. You could change your SQL Syntax to use a "like" expression but that may not be appropriate for your situation. Actually, it might be very appropriate. It's up to you.

Anyway, if you're looking for a really good reference on MS Access Programming I strongly recommend the books by Alison Balter: link

[High_Jumbllama]

My syntax is all ****ed up.
I have a table "Results" that is displaying inside "List0" for form "Lookup". There is a field named "First" inside the "Lookup" form that I need to check to see if it is contained within the "fname" field of "Results".

What I came up with is something like:

Where ( (InStr([Results]![fname],Form_Lookup![First])>0 ) )

Any ideas on what is wrong with my syntax.

[schmosef]

I'm PM my email address to you. You're welcome to email me your mdb file. I'll get it working for you.

[High_Jumbllama]

Nevermind.

(InStr([Results]![fname],[Forms]![Lookup]![First])>0)

[High_Jumbllama]

I realized the easiest way to find out the exact syntax was to put the condition in a query and go into SQL view and copy the text from there.

[schmosef]

I would have used a "like" expression but that's just me. I'm not sure which way would scale better... your way might be faster.

Access lets you mix and match vb and jetsql in a way that other dbs won't let you. it's part of it's power.

[High_Jumbllama]

That'll be the next thing I look up then.

Currently at 62,000 records speed is fast enough for now.