Using List Box to set Filter Criteria in a Query 1

[jennilein]

I have a list box that I would like to use for selecting multiple criteria to be used in a query. It works now with only 1 selection. If I change the multiselect property of the list box to extended - the query doesn't work - it always returns nothing.

How can I use the list box for selecting query criteria using "or" - not "and"?

Thanks.

 

[RoyVidar]

Have a look here thread702-787778.

I think also a look at faq181-2886 (8, 10, 14?) could be recommended;-)

Roy-Vidar

 

[FancyPrairie]

This FAQ FAQ181-5497 contains a function that will do what you want. It will return the Where clause without the word Where. It handles 0 to many list boxes, combo boxes, text boxes, ranges, Option groups, and check boxes. The example the FAQ demonstrates is how to open a report, but the same thing applies to opening a form.

You only have to do 3 things to make it work.

1. Open a new module and highlight the functions in the FAQ and copy them and paste them into the new module (name the new module something like basBuildWhere).

2. Set your tag properties as defined in the FAQ

3. Open your report (or form) as specified in the FAQ

 

[TheAceMan1]

how are ya jennilein . . . . .

Getting the data from the ListBox is easy . . .

[purple]Whats your criteria for determining when to use OR, NOT, AND ?[/purple]

Perhaps an example . . . .

See Ya! . . . . . .

 

[jennilein]

thank you all for your suggestions.

i'm trying to nurse my way throught the FAQ181-5497 - i don't usually write vb code.

the criteria should always be OR -never AND.

to briefly describe what i would like to do...i have a table with sales data & sales codes. I also have a reference table with sales codes & their descriptions. I am creating a report - through selections on a form. The form contains a list box with all sales codes & descriptions, which I would like to use to set the search criteria for the query which runs to open the report.

Does this help?

 

[TheAceMan1]

jennilein . . . . .

Good enough . . . . post the query?

See Ya! . . . . . .

 

[FancyPrairie]

Don't worry about the code in the FAQ, it will take care of itself. Just set up your tag property for the list box. Say, for example, your report's recordsource is set to a query that already has the where clause in it that takes care of the sales code. Something like this: "Select * from WhatEverTable where (Whatever.SalesCode = 1) or (Whatever.SalesCode = 3);". Your tag property for the list box would be Where=Whatever.SalesCode,Number;

 

[jennilein]

Do I copy all the code in the FAQ?

 

[FancyPrairie]

Yes

 

[jennilein]

I appreciate all your help, however, it's still not working. I created a new module and pasted the entire FAQ. I went into the properties of my list box -under the other tab- and placed this under tag: Where=tb_salecodes.salescodedesc,string

What do I put in my query? Right now I set my query just to pull the fields to see if it works...it looks like this:

SELECT [12 Month Table].[salescode], [12 Month Table].partid
FROM [12 Month Table]
WHERE ((([12 Month Table].[salescode]) Like [Forms]![Form1]![List2]))
WITH OWNERACCESS OPTION;

My report is based off this query.

 

[FancyPrairie]

Let me back up a second. You have a list box that contains the sales codes and their descriptions. I'm assuming the Sales Codes is the bound field of the list box and is hidden (column width = 0). So all the user sees is the description for the sales codes. Correct? I'm going to assume that the Sales Code is numeric.

Now, since you want the report to print only those records that match the sales codes selected in the list box, the tag property of the list box should reflect what your SQL statement will be looking for. In your case, the SQL select statement of the report would be "SELECT [12 Month Table].[salescode], [12 Month Table].partid FROM [12 Month Table] WITH OWNERACCESS OPTION;". Note that there is no Where clause at this point.

I'm assuming that [12 Month Table] contains a field [SalesCode]. And this field contains the code that could match one of the items in the list box.

Therefore, the Tag Property of your list box should be Where=[12 Month Table].[SalesCode],Number;

Now, when you open your report, open it like this:

Docmd.OpenReport "rptName",,,BuildWhere(Me)

 

[jennilein]

I'm sorry, but i can't seem to get this to work. I changed the query to match what you wrote - except the sales code is a string and not a number.

I made the name of the code copied from the FAQ - buildwhere.

I then changed the code for the button - to open the query - with the code above, but it won't run! it's catching an error b/c the debug window pops up each time.

Any suggestions...please forgive my inexperience with this stuff!

 

[FancyPrairie]

1. What did you name the module. If you named the module BuildWhere...that's a problem. The function and module names can not be the same. Rename the module to something like basBuildWhere.

2. To test out what BuildWhere will return, enter the following line of code above the Docmd.OpenReport statement.

Msgbox BuildWhere(Me)

3. I guess I was assuming you were using the query to open a report. What are you using the query for?

 

[jennilein]

the query is used to run the report.

i placed the code above the docmd.open report statement, but i'm still getting an error. what should the docmd.openreport statement look like - just the way you wrote it, or should there be more to it?

 

[FancyPrairie]

You should not be getting an error on Msgbox BuildWhere(Me) (assuming the this line of code is placed with the form (i.e. on Button clik or something)).

What error message are you getting? And did you rename the module to something other than BuildWhere?

 

[jennilein]

A pop up box appears with

(tb_salescodes.sales code IN('1P010','1P015'))

then when I click OK - I get a run time error 3075
Syntax error (missing operator) in a query expression '(tb_salescodes.sales code IN('1P010','1P015'))'

Any thoughts?

 

[FancyPrairie]

Post the line of code on which you get the error.

 

[jennilein]

Private Sub Command6_Click()
MsgBox BuildWhere(Me)
DoCmd.OpenReport "rpt_test", , , BuildWhere(Me)
End Sub

 

[FancyPrairie]

I see your problem. When you name you fields/controls and include spaces in the name, then they need to be surrounded by brackets []. Therefore, change the tag property so it looks something like this:

Where=[tb_salescodes].[sales code],string;

However, are you sure you've done this correctly? Your select statement, in a previous post, looks like this:

SELECT [12 Month Table].[salescode], [12 Month Table].partid FROM [12 Month Table] WITH OWNERACCESS OPTION;

tb_salescodes is not part of the select statement. I'm assuming the tb_salescodes is the rowsource for your listbox. Is this correct?

 

[jennilein]

I have a query that runs off a table called 12 Month Table.

The list box is sourced from a table SalesCode.
I didn't set a relationship because I only wanted to use the list box to set the search criteria in my query.

I fixed the naming issue, but it still doesn't work, in fact, I get another pop up window for me to enter the sales code.