Using Text Box values in SQL Queries 1

[dmlatham]

I am currently trying to run a query that will use values in a text box to filter.

For example (taken from the SQL View of the Query Design):

SELECT *
FROM tbl01_CaseTimeDetail
WHERE (((tbl01_CaseTimeDetail.attorney_branch) in ([Forms]![frm02_CaseTime]![txtBranch])));

Now here is the problem. IF ([Forms]![frm02_CaseTime]![txtBranch]) is equal to A, then the query returns values where the attorney_branch is equal to A. If ([Forms]![frm02_CaseTime]![txtBranch]) = A,C (basically more than one value) then the query returns no values. But if I replace ([Forms]![frm02_CaseTime]![txtBranch]) (using Query Design view) with IN ("A","C&quot I get values. Now have have set ([Forms]![frm02_CaseTime]![txtBranch]) to equal ("A","C&quot and I still get no values.

All help is appreciated.

Deborah

 

[braindead2]

try this
WHERE (((tbl01_CaseTimeDetail.attorney_branch) in "(" & [Forms]![frm02_CaseTime]![txtBranch] & &quot" ));

 

[dmlatham]

Thanks for the info Braindead, but that seems to only work with the VBA. I was trying to make the query work using Access 2000 Query Design View.

Any Ideas?

Happy New Year,
Deb

 

[tomschafer]

This should do it...

WHERE (((InStr([Forms]![frm02_CaseTime]![txtBranch], [attorney_branch]))>0))

What was happening with your SQL was the text box is returning a single value="A,B"- So the where clause of your SQL would look like: in ("A,B" not: in ("A", "B&quot.
The where clause I gave you uses the InStr function to determine the position that the characters in your table record are that are on the form. This will work with the example provided- A,B entered on form, if you are searching for whole words- Adam, Bill, it will also work.

 

[dmlatham]

Pezamystik,

The solution you provided worked perfectly.

Thank you for not only providing a solution, but for also explaining why this is happening.

Happy New Year,
Deborah

 

[dmlatham]

Pezamystik,

What do I do if there are no parameters to be passed. Can I use the same command, but somehow modify it?

For example, the User has 6 choices. They can choose some, all or none. None would mean that there is no criteria for that field. How would I represent that using the above code.

Deb

 

[tlbroadbent]

Deb,

Try this modification to the solution provided by Peazmystic.

WHERE
(InStr([Forms]![frm02_CaseTime]![txtBranch],
[attorney_branch])>0
OR [Forms]![frm02_CaseTime]![txtBranch] Is Null);
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dmlatham]

Terry,

Thanks for your reply. I tried your code and it works fine within VB but when I try to paste the same code in the criteria of the query in the 'query design view' it still does not return what I need.

Do you know of a way that I can use the list box selection in the criteria of the query that will select for no criteria (meaning all), one criteria and multiple criteria?

So far I actually have this working (with all the help from code I recieved here) in VB. But I was wondering if there was some way I could directly do this without any VB.

Thanks.

deb.

For you, Nlkflynn check your syntax is dbo.Events.EventID the correct syntax? could it be dbo_Events.EventsID?

 

[tomschafer]

Deb,
My posting will give you results for one to multiple criteria. With Terry's added clause you will get results with no criteria. Make sure you are pasting the Where clause into the SQL View and not the Design View.

 

[dmlatham]

Pezamystik,

I have tried Terry's code and I think the problem is with the 'Null' part of the clause. If nothing is selected txtBranch is blank or "". I have now set it to 'NULL', but I am getting no criteria returned. Is this right? I was thinking that I should get ALL of the values if no criteria is selected.

Any ideas?

Deb

 

[tlbroadbent]

You can modify the second potion of the WHERE clause to include empty strings as well as NULLs. The NZ function will return an empty string if the value is NULL or return the value.

OR NZ([Forms]![frm02_CaseTime]![txtBranch],"&quot=""
Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[dmlatham]

Terry,

Thanks, the information you passed is what I was looking for. I tested it out and it works great.

Deborah