Filter by form using a multiselect listbox

[John Dumay]

Hi All,

I have been able to build a query that is filtered by a selection in a
Listbox, but now i want to use a multi select list box so that two or more
items can be included in the query. I constructed the query pointing to the
Listbox but all I get is an empty dataset. How can this be accomplished?

As always your help is appreciated.

Regards,


John Dumay

 

[Biz Enhancer]

Hi John,

For a multiselect listbox to work as a filter you need to create another
query based on the listbox.

Put this code for execution between the selection of the listbox and running
your main query
****Start of code*****
strSQL = "SELECT * FROM YourTable" 'this is the table that the listbox
is built from.
For i = 0 To List1.ListCount - 1
If List1.Selected(i) Then
strIN = strIN & "'" & List1.Column(0, i) & "',"
End If
Next i
strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere

MyDB.QueryDefs.Delete TempQuery
Set qdf = MyDB.CreateQueryDef(TempQuery, strSQL)

*****End of Code*****
This will create a query named TempQuery which will contain the value
selected in the multiselect listbox. Just link this into your existing query
for it to act as the filter.

HTH
Regards,
Nick

 

[Biz Enhancer]

Forgot to put the declarations at the start of the code!!!
****Start of code*****
Dim MyDB As Database
Dim qdf As QueryDef
Dim i As Integer, strSQL As String
Dim strwhere As String, strIN As String
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM YourTable" 'this is the table that the listbox
is built from.
For i = 0 To List1.ListCount - 1
If List1.Selected(i) Then
strIN = strIN & "'" & List1.Column(0, i) & "',"
End If
Next i
strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere

MyDB.QueryDefs.Delete TempQuery
Set qdf = MyDB.CreateQueryDef(TempQuery, strSQL)

*****End of Code*****

 

[John Dumay]

Hi,

Thanks for the help. The making of a query really helped.

I did have a couple of problems with the code in that it was suited for a
single selection list box not a multi selection list box. In this case you
need to cycle through the array of selected items as shown in the code below.

I alsos added an On Error Resume Next when deleting the Query just in case
it didn't exist because it kept throwing an error when i first ran it.

Hope this adds to your solution.

***************************************************
Private Sub lstDisplayRows1_AfterUpdate()
Dim MyDB As Database
Dim qdf As QueryDef
Dim i As Integer, strSQL As String
Dim strwhere As String, strIN As String
Dim ctl As Control
Dim frm As Form
Dim varItm As Variant

sDocName = "qryTempForDataFilters"

Set MyDB = CurrentDb()
Set frm = Forms!frmBuildDataTables
Set ctl = frm!lstDisplayRows1


strSQL = "SELECT * FROM tblBO_Filter1_Row_Items" 'this is the table
that the listbox is built from.

For Each varItm In ctl.ItemsSelected
strIN = strIN & "'" & ctl.ItemData(varItm) & "',"
Next varItm

strwhere = " WHERE Row_Category in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere
On Error Resume Next
MyDB.QueryDefs.Delete sDocName
Set qdf = MyDB.CreateQueryDef(sDocName, strSQL)


End Sub

*********************************************************

John Dumay

 

[Biz Enhancer]

Hi John,

Glad to have been of some use.

I actually use the code I posted (albeit a little more elaborate) for a
report filter, so it does actually work with multiselect listboxes.
If you add a union 'All' to the listbox source e.g. "SELECT * FROM tbl UNION
SELECT 'All' FROM Tbl;" then it is possible to state
If me.listbox = 'All' then
strSQL = "SELECT * FROM tbl;"
Else
'Run though looping statement to build strSQL
End if
This would negate the need to select everything in the listbox (if that
circumstance arose). As I use 6 multiselect on one form to filter my reports
the "All" entry is very useful.

Regards,
Nick

 

[Rana]

Biz Enhancer,

Thanx for the code, it was very useful. I'm going to be a slight pain but
basically I want to use two multi-list boxes and the 'AND' logical operator
to filter a query.

I've got some other code that allows me to filter using one multi-list box
but I can't seem to get two to work.

Can you please help me out?!

Rana

 

[Biz Enhancer]

Hi Rana,

It is actually really simple using the code below.

****Start Code****
'Create a new tempquery for each listbox
"Don't forget the declarations

strSQL = "SELECT * FROM YourTable1" 'this is the table that the listbox
is built from.
For i = 0 To List1.ListCount - 1
If List1.Selected(i) Then
strIN = strIN & "'" & List1.Column(0, i) & "',"
End If
Next i
strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere

MyDB.QueryDefs.Delete TempQuery1
Set qdf = MyDB.CreateQueryDef(TempQuery1, strSQL)

strSQL = "SELECT * FROM YourTable2" 'this is the table that the listbox
is built from.
For i = 0 To List2.ListCount - 1
If List2.Selected(i) Then
strIN = strIN & "'" & List2.Column(0, i) & "',"
End If
Next i
strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere

MyDB.QueryDefs.Delete TempQuery2
Set qdf = MyDB.CreateQueryDef(TempQuery2, strSQL)

**** End Code****

As long as you are creating a different tempquery for each listbox you can
use them to filter another query.
eg

SELECT tbl.fld1, tbl.fld2 FROM tbl INNER JOIN tempquery1 ON tbl.fld1 =
tempquery.fld1INNER JOIN tempquery2 ON tbl.fld2 = tempquery.fld2;
(yes I know the syntax is not right but it is a concept rather than example)

That should get you on the way.

Don't overlook the simple solutions however, sometimes it is more expedient
to filter one listbox based on the selections of another listbox on the same
form to achieve similar results and a combination of that approach and the
code above can be very powerful.

Regards,
Nick.

 

[Rana]

Hey Nick,

Thanx for your prompt response and the code however, I didn't quite get the
join section.

SELECT tbl.fld1, tbl.fld2 FROM tbl INNER JOIN tempquery1 ON tbl.fld1 =
tempquery.fld1INNER JOIN tempquery2 ON tbl.fld2 = tempquery.fld2;

is tbl.fld1 = the table name and field name of the temp query or the
original table? also, do where do I include the field that initially joins
the two fields together?

Basically, I've filtered "skills" and "months" tables using the multi-list
boxes which are linked together (using relationships) by a "ID no". I want to
join the two temp queries (created when filtering) so that only the "ID no.",
"skills" AND "months" I've filtered come up together in a 'final query'.

Can you please help?!
Thanx
Rana

 

[Biz Enhancer]

Hey Nick,

Thanx for your prompt response and the code however, I didn't quite get the
join section.

SELECT tbl.fld1, tbl.fld2 FROM tbl INNER JOIN tempquery1 ON tbl.fld1 =
tempquery.fld1INNER JOIN tempquery2 ON tbl.fld2 = tempquery.fld2;

is tbl.fld1 = the table name and field name of the temp query or the
original table? also, do where do I include the field that initially joins
the two fields together?

Basically, I've filtered "skills" and "months" tables using the multi-list
boxes which are linked together (using relationships) by a "ID no". I want to
join the two temp queries (created when filtering) so that only the "ID no.",
"skills" AND "months" I've filtered come up together in a 'final query'.

Can you please help?!
Thanx
Rana

In the query 'tbl' is the table you wish to filter. The temp queries are
your filters. When you run the code after making the selections in the
(multiselect) list boxes, the main query, which provides your results, will
filtered based on the selection from your listboxes.

E.g.

Tbl1 fields:
ClientID
Clientname
HairColourID
ShoeSizeID
SuburbID

HairColourTbl fields:
HairColourID
HairColour

ShoeSizeTbl fields:
ShoeSizeID
Shoesize

Listbox1 Rowsource = "SELECT * FROM HairColour"
Listbox2 Rowsource = "SELECT * FROM ShoeSize"

When the code runs, it creates a tempquery from Listbox1 selections and a
tempquery from Listbox2 selections.

Tempquery1 would have:
2,Green
5,Black
7,Purple

Tempquery2 would have:
3,US12
8,US5


The query that provides for results would be:

SELECT Client.ClientID, Client.ClientName, Tempquery1.HairColour,
Tempquery2.ShoeSize FROM Client (INNER JOIN HairColour ON Client.HairColourID
= HairColour.HairColourID) INNER JOIN ShoeSize ON Client.ShoeSizeID =
ShoeSize.ShoeSizeID;

Therefore, the result will be all clientIDs, ClientNames, haircolour, and
shoesize WHERE (haircolour is Green, Black, Or Purple) And (ShoeSize is US5
Or US12).

Changing the Join Structure to LEFT or RIGHT joins will allow some looser
filtering.

Hopefully all that makes more sense.

Regards,
Nick.

 

[Rana]

Nick,

Thanx that cleared up alot! But now I've got another problem. I can't seem
to be able to actually test the code at all! I keep getting an error box
saying [Compile error" Expected: Case] and the table.fielname that's comes
straight after the SELECT gets highlighted. Why does it do that?

I've tried to get help using Microsoft Help and I've gathered that I should
use a Case statement after the SELECT.

Regards,
Rana.

Please bare with me, I'm new at programming in Access.

 

[Biz Enhancer]

Hi Rana,

No excuses or apologies, we all have to start learning from somewhere. This
forum has been a huge help to me in the past. There are some very clever
programmers contributing here just to help people like us.

The error and help are actually correct when using Case statements instead
of IF statements. However, the problem here is that the SELECT statement is
actually an SQL statement and so we need to tell the program that by creating
a query definition.

If this is going to be reused often for the same report, then it is probably
better to create a query using the SQL SELECT statement. This means that the
SELECT statement is not actually in the code and your last action in the code
can be something like -

DoCmd.OpenReport "MyReport"

where the report is based on that query.

Regards,
Nick.

Nick,

Thanx that cleared up alot! But now I've got another problem. I can't seem
to be able to actually test the code at all! I keep getting an error box
saying [Compile error" Expected: Case] and the table.fielname that's comes
straight after the SELECT gets highlighted. Why does it do that?

I've tried to get help using Microsoft Help and I've gathered that I should
use a Case statement after the SELECT.

Regards,
Rana.

Please bare with me, I'm new at programming in Access.

In the query 'tbl' is the table you wish to filter. The temp queries are
your filters. When you run the code after making the selections in the
(multiselect) list boxes, the main query, which provides your results, will
filtered based on the selection from your listboxes.

E.g.

Tbl1 fields:
ClientID
Clientname
HairColourID
ShoeSizeID
SuburbID

HairColourTbl fields:
HairColourID
HairColour

ShoeSizeTbl fields:
ShoeSizeID
Shoesize

Listbox1 Rowsource = "SELECT * FROM HairColour"
Listbox2 Rowsource = "SELECT * FROM ShoeSize"

When the code runs, it creates a tempquery from Listbox1 selections and a
tempquery from Listbox2 selections.

Tempquery1 would have:
2,Green
5,Black
7,Purple

Tempquery2 would have:
3,US12
8,US5


The query that provides for results would be:

SELECT Client.ClientID, Client.ClientName, Tempquery1.HairColour,
Tempquery2.ShoeSize FROM Client (INNER JOIN HairColour ON Client.HairColourID
= HairColour.HairColourID) INNER JOIN ShoeSize ON Client.ShoeSizeID =
ShoeSize.ShoeSizeID;

Therefore, the result will be all clientIDs, ClientNames, haircolour, and
shoesize WHERE (haircolour is Green, Black, Or Purple) And (ShoeSize is US5
Or US12).

Changing the Join Structure to LEFT or RIGHT joins will allow some looser
filtering.

Hopefully all that makes more sense.

Regards,
Nick.

 

[Rana]

Nick,

It works! I finally got it to work! Now I've got to brave adding another
multiselct box but I'm assuming that shouldn't be too difficult if I use the
same concept.

Again, thank a bunch for all your help and patience.

Rana

Hi Rana,

No excuses or apologies, we all have to start learning from somewhere. This
forum has been a huge help to me in the past. There are some very clever
programmers contributing here just to help people like us.

The error and help are actually correct when using Case statements instead
of IF statements. However, the problem here is that the SELECT statement is
actually an SQL statement and so we need to tell the program that by creating
a query definition.

If this is going to be reused often for the same report, then it is probably
better to create a query using the SQL SELECT statement. This means that the
SELECT statement is not actually in the code and your last action in the code
can be something like -

DoCmd.OpenReport "MyReport"

where the report is based on that query.

Regards,
Nick.

Nick,

Thanx that cleared up alot! But now I've got another problem. I can't seem
to be able to actually test the code at all! I keep getting an error box
saying [Compile error" Expected: Case] and the table.fielname that's comes
straight after the SELECT gets highlighted. Why does it do that?

I've tried to get help using Microsoft Help and I've gathered that I should
use a Case statement after the SELECT.

Regards,
Rana.

Please bare with me, I'm new at programming in Access.

:

Hey Nick,

Thanx for your prompt response and the code however, I didn't quite get the
join section.

SELECT tbl.fld1, tbl.fld2 FROM tbl INNER JOIN tempquery1 ON tbl.fld1 =
tempquery.fld1INNER JOIN tempquery2 ON tbl.fld2 = tempquery.fld2;

is tbl.fld1 = the table name and field name of the temp query or the
original table? also, do where do I include the field that initially joins
the two fields together?

Basically, I've filtered "skills" and "months" tables using the multi-list
boxes which are linked together (using relationships) by a "ID no". I want to
join the two temp queries (created when filtering) so that only the "ID no.",
"skills" AND "months" I've filtered come up together in a 'final query'.

Can you please help?!
Thanx
Rana

:

Hi Rana,

It is actually really simple using the code below.

****Start Code****
'Create a new tempquery for each listbox
"Don't forget the declarations

strSQL = "SELECT * FROM YourTable1" 'this is the table that the listbox
is built from.
For i = 0 To List1.ListCount - 1
If List1.Selected(i) Then
strIN = strIN & "'" & List1.Column(0, i) & "',"
End If
Next i
strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere

MyDB.QueryDefs.Delete TempQuery1
Set qdf = MyDB.CreateQueryDef(TempQuery1, strSQL)

strSQL = "SELECT * FROM YourTable2" 'this is the table that the listbox
is built from.
For i = 0 To List2.ListCount - 1
If List2.Selected(i) Then
strIN = strIN & "'" & List2.Column(0, i) & "',"
End If
Next i
strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere

MyDB.QueryDefs.Delete TempQuery2
Set qdf = MyDB.CreateQueryDef(TempQuery2, strSQL)

**** End Code****

As long as you are creating a different tempquery for each listbox you can
use them to filter another query.
eg

SELECT tbl.fld1, tbl.fld2 FROM tbl INNER JOIN tempquery1 ON tbl.fld1 =
tempquery.fld1INNER JOIN tempquery2 ON tbl.fld2 = tempquery.fld2;
(yes I know the syntax is not right but it is a concept rather than example)

That should get you on the way.

Don't overlook the simple solutions however, sometimes it is more expedient
to filter one listbox based on the selections of another listbox on the same
form to achieve similar results and a combination of that approach and the
code above can be very powerful.

Regards,
Nick.



:

Biz Enhancer,

Thanx for the code, it was very useful. I'm going to be a slight pain but
basically I want to use two multi-list boxes and the 'AND' logical operator
to filter a query.

I've got some other code that allows me to filter using one multi-list box
but I can't seem to get two to work.

Can you please help me out?!

Rana

:

Forgot to put the declarations at the start of the code!!!
****Start of code*****
Dim MyDB As Database
Dim qdf As QueryDef
Dim i As Integer, strSQL As String
Dim strwhere As String, strIN As String
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM YourTable" 'this is the table that the listbox
is built from.
For i = 0 To List1.ListCount - 1
If List1.Selected(i) Then
strIN = strIN & "'" & List1.Column(0, i) & "',"
End If
Next i
strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere

MyDB.QueryDefs.Delete TempQuery
Set qdf = MyDB.CreateQueryDef(TempQuery, strSQL)

*****End of Code*****

:

Hi John,

For a multiselect listbox to work as a filter you need to create another
query based on the listbox.

Put this code for execution between the selection of the listbox and running
your main query
****Start of code*****
strSQL = "SELECT * FROM YourTable" 'this is the table that the listbox
is built from.
For i = 0 To List1.ListCount - 1
If List1.Selected(i) Then
strIN = strIN & "'" & List1.Column(0, i) & "',"
End If
Next i
strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere

MyDB.QueryDefs.Delete TempQuery
Set qdf = MyDB.CreateQueryDef(TempQuery, strSQL)

*****End of Code*****
This will create a query named TempQuery which will contain the value
selected in the multiselect listbox. Just link this into your existing query
for it to act as the filter.

HTH
Regards,
Nick

:

Hi All,

I have been able to build a query that is filtered by a selection in a
Listbox, but now i want to use a multi select list box so that two or more
items can be included in the query. I constructed the query pointing to the
Listbox but all I get is an empty dataset. How can this be accomplished?

As always your help is appreciated.

Regards,


John DumayOkay, the way it works is this.

In the query 'tbl' is the table you wish to filter. The temp queries are
your filters. When you run the code after making the selections in the
(multiselect) list boxes, the main query, which provides your results, will
filtered based on the selection from your listboxes.

E.g.

Tbl1 fields:
ClientID
Clientname
HairColourID
ShoeSizeID
SuburbID

HairColourTbl fields:
HairColourID
HairColour

ShoeSizeTbl fields:
ShoeSizeID
Shoesize

Listbox1 Rowsource = "SELECT * FROM HairColour"
Listbox2 Rowsource = "SELECT * FROM ShoeSize"

When the code runs, it creates a tempquery from Listbox1 selections and a
tempquery from Listbox2 selections.

Tempquery1 would have:
2,Green
5,Black
7,Purple

Tempquery2 would have:
3,US12
8,US5


The query that provides for results would be:

SELECT Client.ClientID, Client.ClientName, Tempquery1.HairColour,
Tempquery2.ShoeSize FROM Client (INNER JOIN HairColour ON Client.HairColourID
= HairColour.HairColourID) INNER JOIN ShoeSize ON Client.ShoeSizeID =
ShoeSize.ShoeSizeID;

Therefore, the result will be all clientIDs, ClientNames, haircolour, and
shoesize WHERE (haircolour is Green, Black, Or Purple) And (ShoeSize is US5
Or US12).

Changing the Join Structure to LEFT or RIGHT joins will allow some looser
filtering.

Hopefully all that makes more sense.

Regards,
Nick.

 

[sean]

Hi guys,

Another NOOB here! please help. I've got the code (used Nick's above)
creating the TempQuery but cannot run it. I get the "Data type
mismatch" error. In design view the data from my list box is where it
should be.

Looks like this "In ('9','14','10','11','12','13')" without the end
quotes in the criteria row.

What am I missing? Any help appreciated.


Sean

 

[Rick Brandt]

Hi guys,

Another NOOB here! please help. I've got the code (used Nick's above)
creating the TempQuery but cannot run it. I get the "Data type
mismatch" error. In design view the data from my list box is where it
should be.

Looks like this "In ('9','14','10','11','12','13')" without the end
quotes in the criteria row.

What am I missing? Any help appreciated.

If the field you are filtering is numeric then lose the single quotes around
the numbers. If it is text you need to replace them with double-quotes.

 

[sean]

Rick,

Too easy, works great, thanks mate.

Cheers, Sean

 

[sean]

Anyone,

I have 2 queries that I use to sum data that is filtered by the temp
query. I then join these queries in the record source of a subform
which lies in the main form where my listbox resides. If I open
subform the on it's own it's fine, info is updated and correct, but I
can't get the subform to work using .Requery on after update of the
listbox or using a button to execute. Look as if the .Requery is
working but the data isn't updating. Any suggestions?

Any help/suggestions appreciated

Sean

 

[Rana]

Hey Nick,

I've now got another problem. I also want to filter using a drop down box
along with the multi select boxes. I know that you can do it separately
however I want to write the code on Visual Basic which allows me to create a
3rd temp query (after selection from the drop down box) which I can then join
to a table and use in my final query. The drop down items are value lists
which are the same value lists as those in a table (if that makes sense).

Help!

Rana

Hi Rana,

No excuses or apologies, we all have to start learning from somewhere. This
forum has been a huge help to me in the past. There are some very clever
programmers contributing here just to help people like us.

The error and help are actually correct when using Case statements instead
of IF statements. However, the problem here is that the SELECT statement is
actually an SQL statement and so we need to tell the program that by creating
a query definition.

If this is going to be reused often for the same report, then it is probably
better to create a query using the SQL SELECT statement. This means that the
SELECT statement is not actually in the code and your last action in the code
can be something like -

DoCmd.OpenReport "MyReport"

where the report is based on that query.

Regards,
Nick.

Nick,

Thanx that cleared up alot! But now I've got another problem. I can't seem
to be able to actually test the code at all! I keep getting an error box
saying [Compile error" Expected: Case] and the table.fielname that's comes
straight after the SELECT gets highlighted. Why does it do that?

I've tried to get help using Microsoft Help and I've gathered that I should
use a Case statement after the SELECT.

Regards,
Rana.

Please bare with me, I'm new at programming in Access.

:

Hey Nick,

Thanx for your prompt response and the code however, I didn't quite get the
join section.

SELECT tbl.fld1, tbl.fld2 FROM tbl INNER JOIN tempquery1 ON tbl.fld1 =
tempquery.fld1INNER JOIN tempquery2 ON tbl.fld2 = tempquery.fld2;

is tbl.fld1 = the table name and field name of the temp query or the
original table? also, do where do I include the field that initially joins
the two fields together?

Basically, I've filtered "skills" and "months" tables using the multi-list
boxes which are linked together (using relationships) by a "ID no". I want to
join the two temp queries (created when filtering) so that only the "ID no.",
"skills" AND "months" I've filtered come up together in a 'final query'.

Can you please help?!
Thanx
Rana

:

Hi Rana,

It is actually really simple using the code below.

****Start Code****
'Create a new tempquery for each listbox
"Don't forget the declarations

strSQL = "SELECT * FROM YourTable1" 'this is the table that the listbox
is built from.
For i = 0 To List1.ListCount - 1
If List1.Selected(i) Then
strIN = strIN & "'" & List1.Column(0, i) & "',"
End If
Next i
strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere

MyDB.QueryDefs.Delete TempQuery1
Set qdf = MyDB.CreateQueryDef(TempQuery1, strSQL)

strSQL = "SELECT * FROM YourTable2" 'this is the table that the listbox
is built from.
For i = 0 To List2.ListCount - 1
If List2.Selected(i) Then
strIN = strIN & "'" & List2.Column(0, i) & "',"
End If
Next i
strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere

MyDB.QueryDefs.Delete TempQuery2
Set qdf = MyDB.CreateQueryDef(TempQuery2, strSQL)

**** End Code****

As long as you are creating a different tempquery for each listbox you can
use them to filter another query.
eg

SELECT tbl.fld1, tbl.fld2 FROM tbl INNER JOIN tempquery1 ON tbl.fld1 =
tempquery.fld1INNER JOIN tempquery2 ON tbl.fld2 = tempquery.fld2;
(yes I know the syntax is not right but it is a concept rather than example)

That should get you on the way.

Don't overlook the simple solutions however, sometimes it is more expedient
to filter one listbox based on the selections of another listbox on the same
form to achieve similar results and a combination of that approach and the
code above can be very powerful.

Regards,
Nick.



:

Biz Enhancer,

Thanx for the code, it was very useful. I'm going to be a slight pain but
basically I want to use two multi-list boxes and the 'AND' logical operator
to filter a query.

I've got some other code that allows me to filter using one multi-list box
but I can't seem to get two to work.

Can you please help me out?!

Rana

:

Forgot to put the declarations at the start of the code!!!
****Start of code*****
Dim MyDB As Database
Dim qdf As QueryDef
Dim i As Integer, strSQL As String
Dim strwhere As String, strIN As String
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM YourTable" 'this is the table that the listbox
is built from.
For i = 0 To List1.ListCount - 1
If List1.Selected(i) Then
strIN = strIN & "'" & List1.Column(0, i) & "',"
End If
Next i
strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere

MyDB.QueryDefs.Delete TempQuery
Set qdf = MyDB.CreateQueryDef(TempQuery, strSQL)

*****End of Code*****

:

Hi John,

For a multiselect listbox to work as a filter you need to create another
query based on the listbox.

Put this code for execution between the selection of the listbox and running
your main query
****Start of code*****
strSQL = "SELECT * FROM YourTable" 'this is the table that the listbox
is built from.
For i = 0 To List1.ListCount - 1
If List1.Selected(i) Then
strIN = strIN & "'" & List1.Column(0, i) & "',"
End If
Next i
strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strwhere

MyDB.QueryDefs.Delete TempQuery
Set qdf = MyDB.CreateQueryDef(TempQuery, strSQL)

*****End of Code*****
This will create a query named TempQuery which will contain the value
selected in the multiselect listbox. Just link this into your existing query
for it to act as the filter.

HTH
Regards,
Nick

:

Hi All,

I have been able to build a query that is filtered by a selection in a
Listbox, but now i want to use a multi select list box so that two or more
items can be included in the query. I constructed the query pointing to the
Listbox but all I get is an empty dataset. How can this be accomplished?

As always your help is appreciated.

Regards,


John DumayOkay, the way it works is this.

In the query 'tbl' is the table you wish to filter. The temp queries are
your filters. When you run the code after making the selections in the
(multiselect) list boxes, the main query, which provides your results, will
filtered based on the selection from your listboxes.

E.g.

Tbl1 fields:
ClientID
Clientname
HairColourID
ShoeSizeID
SuburbID

HairColourTbl fields:
HairColourID
HairColour

ShoeSizeTbl fields:
ShoeSizeID
Shoesize

Listbox1 Rowsource = "SELECT * FROM HairColour"
Listbox2 Rowsource = "SELECT * FROM ShoeSize"

When the code runs, it creates a tempquery from Listbox1 selections and a
tempquery from Listbox2 selections.

Tempquery1 would have:
2,Green
5,Black
7,Purple

Tempquery2 would have:
3,US12
8,US5


The query that provides for results would be:

SELECT Client.ClientID, Client.ClientName, Tempquery1.HairColour,
Tempquery2.ShoeSize FROM Client (INNER JOIN HairColour ON Client.HairColourID
= HairColour.HairColourID) INNER JOIN ShoeSize ON Client.ShoeSizeID =
ShoeSize.ShoeSizeID;

Therefore, the result will be all clientIDs, ClientNames, haircolour, and
shoesize WHERE (haircolour is Green, Black, Or Purple) And (ShoeSize is US5
Or US12).

Changing the Join Structure to LEFT or RIGHT joins will allow some looser
filtering.

Hopefully all that makes more sense.

Regards,
Nick.