Search using textbox - I'm sure this is simple syntax!

[idle]

CODE:


SELECT Tbl_Main.[id#], Tbl_Main.descrip, Tbl_Main.install, Tbl_Main.category, Tbl_Main.othertxt3 FROM Tbl_Main WHERE ((([Tbl_Main].[descrip]) Or [Tbl_Main].[id#] Like "*" & ([Forms]![Tbl_Main Query]![textSearch].[Text] & "*") And [Forms]![Tbl_Main Query]![textSearch].[Text]<>""));

I have underlined where I believe my error is occuring. I'm wanting to search Tbl_Main, but I want to compare the text box entry to BOTH "descrip" and "id#". But, when I type, it shows nothing in this form.

Thank you!

[ranman256]

You wouldnt search both fields....the [id#] will be exact (dont search this),

the [descrip] field is where you put the wildcard...: [Tbl_Main].[descrip] Like "*" & [Forms]![Tbl_Main Query]![textSearch]

[idle]

Okay, then how could I search the ID#?

[rpeare]

You can search your ID field with wildcards too, just be aware you're going to capture a bunch of stuff you may not want.

Your WHERE statement would be

WHERE ((([Tbl_Main].[descrip]) Like "*" & [Forms]![Tbl_Main Query]![textSearch].[Text] & "*" OR ([tbl_main].[ID#]) like "*" & [forms]![tbl_main Query]![textSearch].[Text] & "*") And [Forms]![Tbl_Main Query]![textSearch].[Text]<>""))

What are you trying to do with the

And [Forms]![Tbl_Main Query]![textSearch].[Text]<>""

portion of this where clause? Return no results if there's nothing typed in? Return everything if nothing is typed in?

If you want to return nothing (no records) if nothing is typed in you can prevent the query from running at all just by checking for a null value in the field.

If you are trying to return all records if nothign is typed in you don't need this statement at all.

[idle]

You can search your ID field with wildcards too, just be aware you're going to capture a bunch of stuff you may not want.

Your WHERE statement would be

WHERE ((([Tbl_Main].[descrip]) Like "*" & [Forms]![Tbl_Main Query]![textSearch].[Text] & "*" OR ([tbl_main].[ID#]) like "*" & [forms]![tbl_main Query]![textSearch].[Text] & "*") And [Forms]![Tbl_Main Query]![textSearch].[Text]<>""))

What are you trying to do with the

And [Forms]![Tbl_Main Query]![textSearch].[Text]<>""

portion of this where clause? Return no results if there's nothing typed in? Return everything if nothing is typed in?

If you want to return nothing (no records) if nothing is typed in you can prevent the query from running at all just by checking for a null value in the field.

If you are trying to return all records if nothign is typed in you don't need this statement at all.

Thank you, I will try out the new WHERE statement.

My goal is:
-Return NOTHING if there is nothing typed in. (I do not know how to check for a NULL value, although the logic of it does make sense)