Wanted: blank input strings to not show all entries in queries 2

[IcePak]

Hi everyone

I have a form that has search options on it. One in particular involves three text areas where you can enter words (one word in each) to search a table for. I used three OR statements in the SELECT portion of my query SQL for each field that I'm searching through.

It works fine if the user enters a word in each text box then hits the query macro (in other words, it searches the table for any entry that has one of the three inputted words). Problem is, when any of the boxes are left BLANK, the query returns ALL entries.

I've tried altering the default value for the text boxes, but I still want the boxes to appear blank. I want it so that, in essence, if you leave two boxes blank, it means that you only want to search for one word.

I wondering if it is because there is one field in my table that has no data, and I assume set to the default "Null". But there should still be a way.

Any help that any of you kind people out there may be able to offer would be greatly appreciated.

Thanks
Mike

 

[Maquis]

Use ands instead of ors. Also if a user leaves a text box blank then don't include it in your query criteria.

strSQL = "Select "*" from tablename where "
strAND = ""
If not(IsNull(textbox1)) then
strSQL = strSQL & "field1 = " & me!textbox1
strAND = " and "
end if

If not(IsNull(textbox2)) then
strSQL = strSQL & strAND & "field2 = " & me!textbox2
strAND = " and "
end if

If not(isnull(textbox3)) then
strsql = strsql & strAND & "field3 = " & me!textbox3
end if

docmd.runsql strSQL



The code assumes that at least one box is filled in. You'll need to tweak it a bit to get it working with your tables, but the general idea is there.
Maq B-)
<insert witty signature here>

 

[IcePak]

Thanks for the expedient reply Maq.

I can follow your code, but I guess I don't know where to put it. I'm really a novice at SQL, I just really only know how to view it and do minor alterations to already-existing code.

If there's any way you you could break down the process on a beginner level that would be great [as to where to enter that, especially if its in a module (and if it is, how to create and apply a module since i really know nothing about them)]

Thanks
Mike

 

[Maquis]

Mike, how are you running the query now? You already have a form with textboxes for the user to fill in, so I'm assuming there's a button on this form that the user clicks when he's ready to run the query. If so, just put this code in the on_click event of the button.

If this isn't the case, then explain how you have it set up. Maq B-)
<insert witty signature here>

 

[IcePak]

Hey Maq, thanks again.

OK, I'm realizing how much there is to learn about this program. You're right about how my form works, I had assigned a macro to run the query. I've never used the Event settings. I know that you can find On_Click by accessing the properties of the macro button assigned to run the query. I'm assuming you enter this code in the &quot;where&quot; box at the bottom of the macro design view.

Now if that's the case...
How can I alter that code you gave me, I'm not exactly sure what I'm supposed to and not supposed to personalize. And should I still call up the query in the code and where do I do that?

Geez, I don't even know what half of those commands you gave in code mean.

I'm sorry, I appreciate your patience.

Mike

 

[tlbroadbent]

1) RunSQL will not work for Select queries. Its purpose is to run Action queries such as Delete, Update, Alter, etc.

2) If you are running the query from a macro, you must have created and stored a query. Is this true? Do you reference the text boxes on the form from the query? If this is so, your query should be like one of the following.
[tt]
Select * From TableA
Where (col1=forms!frmname!text1
Or forms!frmname!text1 Is Null)
And (col2=forms!frmname!text2
Or forms!frmname!text2 Is Null)
And (col3=forms!frmname!text3
Or forms!frmname!text3 Is Null)[/tt]

Alternatively, you can use the LIKE predicate for string values. This is more versatile but less efficient.
[tt]
Select * From TableA
Where (col1 Like &quot;*&quot; & forms!frmname!text1 &quot;*&quot
And (col2 Like &quot;*&quot; & forms!frmname!text2 &quot;*&quot
And (col3 Like &quot;*&quot; & forms!frmname!text3 &quot;*&quot[/tt] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Maquis]

Ok, while I was in the midst of writing a novel here, Terry beat me to the submit button.

If you don't know how to code in VB, his solution is best. And I didn't realize the runSql command wouldn't run a select statement. I use it so often myself, but like Terry said I don't think I've ever tried to use it for a select query.

However, if you are serious about designing databases, you will want to learn VB. You'll find that you can do so much more in Access with VB than you can with macros and queries alone. If possible, see if your boss will pay for a VB class.
Maq B-)
<insert witty signature here>

 

[IcePak]

thanks alot guys!