Multi Select Listbox or subform!

[Carma via AccessMonster.com]

Basically I have a list or items I want to represent on a form and allow the
user to select any one or more items from that list. I want the items
selected to be used as my filter criteria in my query. Don't care if the
list is represented as a Listbox or subform, just want way to use the
selected items for my filter.

From what I've read it seems like I need to create some code to make this
work, but as I'm not really good with code I would really need someone's
patience to help me out.

So say I have a form called frmmain and either a list box named Listbox1 or a
sub form called frmMainSub, how can I make this work?

I've tried this with a listbox and selected MultiSelect property to Extended
but when I reference the listbox in the query I get null results as I learned
from the help file is what its suppose to do.

Please help!

 

[Douglas J. Steele]

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web"

 

[Carma via AccessMonster.com]

Hi,

Thanks for the reply. I have reviewed the information you sent me but since
I have next to no experience writing code in access I'm not sure how to apply
the sample code below which you sent me:

******************** Code Start ************************
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))
'******************** Code end ************************

But I can tell you the structure of my form and perhaps you'd be gracious
enough to help me modify the code.

My form is: Forms!frmPosition]
The listbox is: Forms![frmPosition]![ListPosExcl]
The listbox is based upon query: qryListPosExcl.[PositonExclusion]

Is there anything else you need to know?

Should I create a text box to store the results of the query in to be
referenced by the query?

Thanks for your help!

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web"

Basically I have a list or items I want to represent on a form and allow
the

[quoted text clipped - 18 lines]

Please help!

 

[Douglas J. Steele]

Your original post said " I want the items selected to be used as my filter
criteria in my query." What exactly do you mean by that? How do you intend
to filter a query? What's the query being used for?

What's the name of the field on which you wish to filter, and what's its
data type?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

Thanks for the reply. I have reviewed the information you sent me but
since
I have next to no experience writing code in access I'm not sure how to
apply
the sample code below which you sent me:

******************** Code Start ************************
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))
'******************** Code end ************************

But I can tell you the structure of my form and perhaps you'd be gracious
enough to help me modify the code.

My form is: Forms!frmPosition]
The listbox is: Forms![frmPosition]![ListPosExcl]
The listbox is based upon query: qryListPosExcl.[PositonExclusion]

Is there anything else you need to know?

Should I create a text box to store the results of the query in to be
referenced by the query?

Thanks for your help!

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web"

Basically I have a list or items I want to represent on a form and allow
the

[quoted text clipped - 18 lines]

Please help!

 

[Carma via AccessMonster.com]

Hi,

Ok I have a table called tblEmployees which has a list of all employees in
our organization, over 33k which is imported from another system. I
basically want the user to be able to filter this table based upon certain
employee characteristics(or any combination) such as: Language(english,
french, German, etc), Geographic region(Newfoundland, Ontario, Quebec,
Alberta, etc), Classification(FI01, FI02, FI03, etc) and a few others.

So I created the form frmPostions with several multiselect list boxes on it
so that the user can select items from any one or more list boxes and then I
wanted to create a query to filter the table based upon the selections to
produce a report. I added tick boxes beside each list box so that the user
can identify which listboxes will be used in the filter and my query will
identify the selected ones.

I was able to succesfully make the query filter correctly when the user was
only allowed to select one item from each list box but I haven't been
successful with the multiselect listboxes.

In the example below the name of the field I wish to filter is
PositionExclusion. its data type is text as it will be for all five fields.

I hope this helps clarify the situation a little.

Thank you so much for your great assistance.

Your original post said " I want the items selected to be used as my filter
criteria in my query." What exactly do you mean by that? How do you intend
to filter a query? What's the query being used for?

What's the name of the field on which you wish to filter, and what's its
data type?

Hi,

[quoted text clipped - 43 lines]

 

[Douglas J. Steele]

Okay, you really won't be working with the query at all.

Create a query that returns all of the data and base your report on that.
Then, when you open the report using the OpenReport method, pass a where
condition to limit what's actually shown on the report.

Let's assume you have three multiselect list boxes named lstLanguage,
lstRegion and lstClass (with corresponding check boxes chkLanguage,
chkRegion and chkClass.

You'd also want a button on the form to open the report. In the Click event
of that button, you'd want something like:

Private Sub cmdPrint_Click()
Dim strTemp As String
Dim strWhere As String
Dim varSelected As Variant

If Me.chkLanguage = True Then
If Me.lstLanguage.ItemsSelected.Count > 0 Then
strTemp = vbNullString
For Each varSelected in Me.lstLanguage.ItemsSelected
strTemp = strTemp & "'" & Me.lstLanguage.ItemData(varSelected) & "',
"
Next varSelected
strWhere = strWhere & _
"Language IN (" & Left(strTemp, Len(strTemp) - 2) & ") AND "
End If
End If

If Me.chkRegion = True Then
If Me.lstRegion.ItemsSelected.Count > 0 Then
strTemp = vbNullString
For Each varSelected in Me.lstRegion.ItemsSelected
strTemp = strTemp & "'" & Me.lstRegion.ItemData(varSelected) & "', "
Next varSelected
strWhere = strWhere & _
"Region IN (" & Left(strTemp, Len(strTemp) - 2) & ") AND "
End If
End If

If Me.chkClass = True Then
If Me.lstClass.ItemsSelected.Count > 0 Then
strTemp = vbNullString
For Each varSelected in Me.lstClass.ItemsSelected
strTemp = strTemp & "'" & Me.lstClass.ItemData(varSelected) & "', "
Next varSelected
strWhere = strWhere & _
"Classification IN (" & Left(strTemp, Len(strTemp) - 2) & ") AND "
End If
End If

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenReport "MyReport", acPrintPreview, , strWhere

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

Ok I have a table called tblEmployees which has a list of all employees in
our organization, over 33k which is imported from another system. I
basically want the user to be able to filter this table based upon certain
employee characteristics(or any combination) such as: Language(english,
french, German, etc), Geographic region(Newfoundland, Ontario, Quebec,
Alberta, etc), Classification(FI01, FI02, FI03, etc) and a few others.

So I created the form frmPostions with several multiselect list boxes on
it
so that the user can select items from any one or more list boxes and then
I
wanted to create a query to filter the table based upon the selections to
produce a report. I added tick boxes beside each list box so that the
user
can identify which listboxes will be used in the filter and my query will
identify the selected ones.

I was able to succesfully make the query filter correctly when the user
was
only allowed to select one item from each list box but I haven't been
successful with the multiselect listboxes.

In the example below the name of the field I wish to filter is
PositionExclusion. its data type is text as it will be for all five
fields.

I hope this helps clarify the situation a little.

Thank you so much for your great assistance.

Your original post said " I want the items selected to be used as my
filter
criteria in my query." What exactly do you mean by that? How do you intend
to filter a query? What's the query being used for?

What's the name of the field on which you wish to filter, and what's its
data type?

Hi,

[quoted text clipped - 43 lines]

Please help!

 

[Carma via AccessMonster.com]

Hi again,

ok for my testing purposes I've named all my controls using the names you
provided just so that i can run your code almost exactly. I'm just running
it based upon one list box now until its nailed down. But I still have 3
problems one of which you wouldn't have known about because i forgot to
provide one piece of information(null as a valid list selection).

1. The code you provided works when I select only one item from the listbox
but when I select two or more I get the following error: Run-time error
'3075'. Syntax error in string in query expression '(Language in ('P',"'Y',))
'.
Note that P and Y are the two selections I made in the listbox.
2. Second problem is that when I select the button it prints the report
instead of opening it.
3. Third problem is that each list all contain an item with a null value.
But when I select that null value only the report doesn't show any records
even though there are many records where the Language field is null.

here is the code i've attached to my button:

Private Sub Command103_Click()

Dim strTemp As String
Dim strWhere As String
Dim varSelected As Variant

If Me.chklanguage = True Then
If Me.lstLanguage.ItemsSelected.Count > 0 Then
strTemp = vbNullString
For Each varSelected In Me.lstLanguage.ItemsSelected
strTemp = strTemp & "'" & Me.lstLanguage.ItemData(varSelected) & "',
"""
Next varSelected
strWhere = strWhere & _
"Language IN (" & Left(strTemp, Len(strTemp) - 2) & ") AND "
End If
End If


If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenReport "rptFilteredPositions", acPrintPreview, , strWhere

Thank-you!

Okay, you really won't be working with the query at all.

Create a query that returns all of the data and base your report on that.
Then, when you open the report using the OpenReport method, pass a where
condition to limit what's actually shown on the report.

Let's assume you have three multiselect list boxes named lstLanguage,
lstRegion and lstClass (with corresponding check boxes chkLanguage,
chkRegion and chkClass.

You'd also want a button on the form to open the report. In the Click event
of that button, you'd want something like:

Private Sub cmdPrint_Click()
Dim strTemp As String
Dim strWhere As String
Dim varSelected As Variant

If Me.chkLanguage = True Then
If Me.lstLanguage.ItemsSelected.Count > 0 Then
strTemp = vbNullString
For Each varSelected in Me.lstLanguage.ItemsSelected
strTemp = strTemp & "'" & Me.lstLanguage.ItemData(varSelected) & "',
"
Next varSelected
strWhere = strWhere & _
"Language IN (" & Left(strTemp, Len(strTemp) - 2) & ") AND "
End If
End If

If Me.chkRegion = True Then
If Me.lstRegion.ItemsSelected.Count > 0 Then
strTemp = vbNullString
For Each varSelected in Me.lstRegion.ItemsSelected
strTemp = strTemp & "'" & Me.lstRegion.ItemData(varSelected) & "', "
Next varSelected
strWhere = strWhere & _
"Region IN (" & Left(strTemp, Len(strTemp) - 2) & ") AND "
End If
End If

If Me.chkClass = True Then
If Me.lstClass.ItemsSelected.Count > 0 Then
strTemp = vbNullString
For Each varSelected in Me.lstClass.ItemsSelected
strTemp = strTemp & "'" & Me.lstClass.ItemData(varSelected) & "', "
Next varSelected
strWhere = strWhere & _
"Classification IN (" & Left(strTemp, Len(strTemp) - 2) & ") AND "
End If
End If

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenReport "MyReport", acPrintPreview, , strWhere

End Sub

Hi,

[quoted text clipped - 41 lines]

 

[Douglas J. Steele]

1. The code you provided works when I select only one item from the
listbox
but when I select two or more I get the following error: Run-time error
'3075'. Syntax error in string in query expression '(Language in
('P',"'Y',))
'.
Note that P and Y are the two selections I made in the listbox.

Your code has "', """ at the end of the line (" ', " " "). It should be "',
" (" ', ")

2. Second problem is that when I select the button it prints the report
instead of opening it.

Using acPrintPreview with the OpenReport method should just open the report,
not print it. Do you have code associated with the report to cause it to
print itself?

3. Third problem is that each list all contain an item with a null value.
But when I select that null value only the report doesn't show any records
even though there are many records where the Language field is null.

You cannot use this approach for Nulls. Null is a special case, and you
cannot compare a value to Null: you must use IS NULL (or the IsNull VBA
function). Are you going to need to check for Null in conjunction with other
values? You could try something like:

Private Sub Command103_Click()

Dim booNull As Boolean
Dim lngLen As Long
Dim strTemp As String
Dim strWhere As String
Dim varSelected As Variant

If Me.chklanguage = True Then
If Me.lstLanguage.ItemsSelected.Count > 0 Then
booNull = False
strTemp = vbNullString
For Each varSelected In Me.lstLanguage.ItemsSelected
If IsNull(Me.lstLanguage.ItemData(varSelected) = True Then
booNull = True
Else
strTemp = strTemp & "'" & Me.lstLanguage.ItemData(varSelected) &
"', "
End If
Next varSelected
lngLen = Len(strTemp) - 2
If lngLen > 0 Then
strWhere = strWhere & _
"(Language IN (" & Left(strTemp, lngLen) & ") "
If booNull = True Then
strWhere = strWhere & " OR Language IS NULL"
End If
strWhere = strWhere & ") AND "
Else
strWhere = strWhere & "Language IS NULL AND "
End If
End If
End If

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenReport "rptFilteredPositions", acPrintPreview, , strWhere

 

[Douglas J. Steele]

Just realized that the answer to the first question is a little confusing.

The end of your line

strTemp = strTemp & "'" & Me.lstLanguage.ItemData(varSelected) & "', "

is wrong. What you were concatenating at the end of the line ended with
three double quotes. It should only end with one double quote.

 

[Carma via AccessMonster.com]

1. Ok your correction you provided for problem one works perfectly

2. I don't have any code of the report which would cause it to print itself
but i just experimented and changed acprintPreview to acPreview and that
problem fixed itself.

3. Yes it is possible that a user may select Null along with other items in
the listbox at the same time. I copied your code in and it still doesn't
give me any results when I select Null by itself or Null in conjunction with
another item.
I made one minor difference to your code which was to add a closing
parenthesis in the following line:

If IsNull(Me.lstLanguage.ItemData(varSelected) = True Then

Here is my full code with the minor adjustments:

Private Sub Command103_Click()

Dim booNull As Boolean
Dim lngLen As Long
Dim strTemp As String
Dim strWhere As String
Dim varSelected As Variant

If Me.chklanguage = True Then
If Me.lstLanguage.ItemsSelected.Count > 0 Then
booNull = False
strTemp = vbNullString
For Each varSelected In Me.lstLanguage.ItemsSelected
If IsNull(Me.lstLanguage.ItemData(varSelected)) = True Then
booNull = True
Else
strTemp = strTemp & "'" & Me.lstLanguage.ItemData(varSelected) & "',
"
End If
Next varSelected
lngLen = Len(strTemp) - 2
If lngLen > 0 Then
strWhere = strWhere & _
"(Language IN (" & Left(strTemp, lngLen) & ") "
If booNull = True Then
strWhere = strWhere & " OR Language IS NULL"
End If
strWhere = strWhere & ") AND "
Else
strWhere = strWhere & "Language IS NULL AND "
End If
End If
End If

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenReport "rptFilteredPositions", acPreview, , strWhere

End Sub

1. The code you provided works when I select only one item from the
listbox

[quoted text clipped - 3 lines]

'.
Note that P and Y are the two selections I made in the listbox.

Your code has "', """ at the end of the line (" ', " " "). It should be "',
" (" ', ")

2. Second problem is that when I select the button it prints the report
instead of opening it.

Using acPrintPreview with the OpenReport method should just open the report,
not print it. Do you have code associated with the report to cause it to
print itself?

3. Third problem is that each list all contain an item with a null value.
But when I select that null value only the report doesn't show any records
even though there are many records where the Language field is null.

You cannot use this approach for Nulls. Null is a special case, and you
cannot compare a value to Null: you must use IS NULL (or the IsNull VBA
function). Are you going to need to check for Null in conjunction with other
values? You could try something like:

Private Sub Command103_Click()

Dim booNull As Boolean
Dim lngLen As Long
Dim strTemp As String
Dim strWhere As String
Dim varSelected As Variant

If Me.chklanguage = True Then
If Me.lstLanguage.ItemsSelected.Count > 0 Then
booNull = False
strTemp = vbNullString
For Each varSelected In Me.lstLanguage.ItemsSelected
If IsNull(Me.lstLanguage.ItemData(varSelected) = True Then
booNull = True
Else
strTemp = strTemp & "'" & Me.lstLanguage.ItemData(varSelected) &
"', "
End If
Next varSelected
lngLen = Len(strTemp) - 2
If lngLen > 0 Then
strWhere = strWhere & _
"(Language IN (" & Left(strTemp, lngLen) & ") "
If booNull = True Then
strWhere = strWhere & " OR Language IS NULL"
End If
strWhere = strWhere & ") AND "
Else
strWhere = strWhere & "Language IS NULL AND "
End If
End If
End If

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenReport "rptFilteredPositions", acPrintPreview, , strWhere

Carma

 

[Carma via AccessMonster.com]

Oops below I should have said here is your code(as it certainly isn't mine)
with my minor adjustments. Sorry about that.

1. Ok your correction you provided for problem one works perfectly

2. I don't have any code of the report which would cause it to print itself
but i just experimented and changed acprintPreview to acPreview and that
problem fixed itself.

3. Yes it is possible that a user may select Null along with other items in
the listbox at the same time. I copied your code in and it still doesn't
give me any results when I select Null by itself or Null in conjunction with
another item.
I made one minor difference to your code which was to add a closing
parenthesis in the following line:

If IsNull(Me.lstLanguage.ItemData(varSelected) = True Then

Here is my full code with the minor adjustments:

Private Sub Command103_Click()

Dim booNull As Boolean
Dim lngLen As Long
Dim strTemp As String
Dim strWhere As String
Dim varSelected As Variant

If Me.chklanguage = True Then
If Me.lstLanguage.ItemsSelected.Count > 0 Then
booNull = False
strTemp = vbNullString
For Each varSelected In Me.lstLanguage.ItemsSelected
If IsNull(Me.lstLanguage.ItemData(varSelected)) = True Then
booNull = True
Else
strTemp = strTemp & "'" & Me.lstLanguage.ItemData(varSelected) & "',
"
End If
Next varSelected
lngLen = Len(strTemp) - 2
If lngLen > 0 Then
strWhere = strWhere & _
"(Language IN (" & Left(strTemp, lngLen) & ") "
If booNull = True Then
strWhere = strWhere & " OR Language IS NULL"
End If
strWhere = strWhere & ") AND "
Else
strWhere = strWhere & "Language IS NULL AND "
End If
End If
End If

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenReport "rptFilteredPositions", acPreview, , strWhere

End Sub
[quoted text clipped - 60 lines]

DoCmd.OpenReport "rptFilteredPositions", acPrintPreview, , strWhere

Carma

 

[Carma via AccessMonster.com]

Upon some further testing when I select the Null item by itself nothing
appears on the report, but there are a lot of records which have Null in
field Language. But when you select Null and another item the report does
show the records for the non null item but not for the Null item.

In addition I found out that if I don't tick chkLanguage and click the button
the report shows All records when it shouldn't show any. The same is true if
chkLanguage is checked and no items in the listbox are selected.

1. Ok your correction you provided for problem one works perfectly

2. I don't have any code of the report which would cause it to print itself
but i just experimented and changed acprintPreview to acPreview and that
problem fixed itself.

3. Yes it is possible that a user may select Null along with other items in
the listbox at the same time. I copied your code in and it still doesn't
give me any results when I select Null by itself or Null in conjunction with
another item.
I made one minor difference to your code which was to add a closing
parenthesis in the following line:

If IsNull(Me.lstLanguage.ItemData(varSelected) = True Then

Here is my full code with the minor adjustments:

Private Sub Command103_Click()

Dim booNull As Boolean
Dim lngLen As Long
Dim strTemp As String
Dim strWhere As String
Dim varSelected As Variant

If Me.chklanguage = True Then
If Me.lstLanguage.ItemsSelected.Count > 0 Then
booNull = False
strTemp = vbNullString
For Each varSelected In Me.lstLanguage.ItemsSelected
If IsNull(Me.lstLanguage.ItemData(varSelected)) = True Then
booNull = True
Else
strTemp = strTemp & "'" & Me.lstLanguage.ItemData(varSelected) & "',
"
End If
Next varSelected
lngLen = Len(strTemp) - 2
If lngLen > 0 Then
strWhere = strWhere & _
"(Language IN (" & Left(strTemp, lngLen) & ") "
If booNull = True Then
strWhere = strWhere & " OR Language IS NULL"
End If
strWhere = strWhere & ") AND "
Else
strWhere = strWhere & "Language IS NULL AND "
End If
End If
End If

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenReport "rptFilteredPositions", acPreview, , strWhere

End Sub
[quoted text clipped - 60 lines]

DoCmd.OpenReport "rptFilteredPositions", acPrintPreview, , strWhere

Carma

 

[Douglas J. Steele]

What's actually in the list box for the Null entries? I assumed it was a
Null value, but perhaps you've got the word Null there instead?

If you have a specific value in the list box, change

If IsNull(Me.lstLanguage.ItemData(varSelected)) = True Then

to

If Me.lstLanguage.ItemData(varSelected) = "Null" Then

(or whatever the value is)

Sorry about the missing parenthesis, btw.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Upon some further testing when I select the Null item by itself nothing
appears on the report, but there are a lot of records which have Null in
field Language. But when you select Null and another item the report does
show the records for the non null item but not for the Null item.

In addition I found out that if I don't tick chkLanguage and click the
button
the report shows All records when it shouldn't show any. The same is true
if
chkLanguage is checked and no items in the listbox are selected.

1. Ok your correction you provided for problem one works perfectly

2. I don't have any code of the report which would cause it to print
itself
but i just experimented and changed acprintPreview to acPreview and that
problem fixed itself.

3. Yes it is possible that a user may select Null along with other items
in
the listbox at the same time. I copied your code in and it still doesn't
give me any results when I select Null by itself or Null in conjunction
with
another item.
I made one minor difference to your code which was to add a closing
parenthesis in the following line:

If IsNull(Me.lstLanguage.ItemData(varSelected) = True Then

Here is my full code with the minor adjustments:

Private Sub Command103_Click()

Dim booNull As Boolean
Dim lngLen As Long
Dim strTemp As String
Dim strWhere As String
Dim varSelected As Variant

If Me.chklanguage = True Then
If Me.lstLanguage.ItemsSelected.Count > 0 Then
booNull = False
strTemp = vbNullString
For Each varSelected In Me.lstLanguage.ItemsSelected
If IsNull(Me.lstLanguage.ItemData(varSelected)) = True Then
booNull = True
Else
strTemp = strTemp & "'" & Me.lstLanguage.ItemData(varSelected) &
"',
"
End If
Next varSelected
lngLen = Len(strTemp) - 2
If lngLen > 0 Then
strWhere = strWhere & _
"(Language IN (" & Left(strTemp, lngLen) & ") "
If booNull = True Then
strWhere = strWhere & " OR Language IS NULL"
End If
strWhere = strWhere & ") AND "
Else
strWhere = strWhere & "Language IS NULL AND "
End If
End If
End If

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenReport "rptFilteredPositions", acPreview, , strWhere

End Sub

1. The code you provided works when I select only one item from the
listbox

[quoted text clipped - 60 lines]

DoCmd.OpenReport "rptFilteredPositions", acPrintPreview, , strWhere

Carma

 

[Carma via AccessMonster.com]

You assumed correctly, it is a null value not the word "Null". For further
information the list box is based upon a query. The query simply provides me
with all unique values in the the Language field in tblLanguage and the null
value is the most predominate.

What's actually in the list box for the Null entries? I assumed it was a
Null value, but perhaps you've got the word Null there instead?

If you have a specific value in the list box, change

If IsNull(Me.lstLanguage.ItemData(varSelected)) = True Then

to

If Me.lstLanguage.ItemData(varSelected) = "Null" Then

(or whatever the value is)

Sorry about the missing parenthesis, btw.

Upon some further testing when I select the Null item by itself nothing
appears on the report, but there are a lot of records which have Null in

[quoted text clipped - 77 lines]

 

[Douglas J. Steele]

It doesn't make sense, then, that it wouldn't work when all you've selected
from the list is the Null entry. Let's go through the code:

' They have to check the check box
If Me.chklanguage = True Then

' They have to have selected at least one entry in the list box
If Me.lstLanguage.ItemsSelected.Count > 0 Then

' Initialize things
booNull = False
strTemp = vbNullString

' Look at each entry selected in the list box
For Each varSelected In Me.lstLanguage.ItemsSelected

' If the selected entry is Null, set the flag booNull to indicated that
If IsNull(Me.lstLanguage.ItemData(varSelected)) = True Then
booNull = True

' If the selected entry isn't Null, add it to the variable strTemp
Else
strTemp = strTemp & "'" & Me.lstLanguage.ItemData(varSelected) & "',
"
End If
Next varSelected

' We've now looked all all of the selected items in the list box.
' Check whether anything was added to strTemp
lngLen = Len(strTemp) - 2

' If something was added to strTemp, create the Where clause
' using strTemp. If booNull is True, add an IS NULL clause
' to the Where clause.
If lngLen > 0 Then
strWhere = strWhere & _
"(Language IN (" & Left(strTemp, lngLen) & ") "
If booNull = True Then
strWhere = strWhere & " OR Language IS NULL"
End If
strWhere = strWhere & ") AND "

' If nothing was added to strTemp, we know that booNull must be True.
' (Remember, we wouldn't be here unless at least one entry was selected
' in the listbox, and since nothing was added to strTemp, then that one
' entry must have been Null)
Else
strWhere = strWhere & "Language IS NULL AND "
End If
End If
End If

Can you single-step through the code and see whether that's actually what's
occurring? What's actually being put into strWhere?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You assumed correctly, it is a null value not the word "Null". For
further
information the list box is based upon a query. The query simply provides
me
with all unique values in the the Language field in tblLanguage and the
null
value is the most predominate.

What's actually in the list box for the Null entries? I assumed it was a
Null value, but perhaps you've got the word Null there instead?

If you have a specific value in the list box, change

If IsNull(Me.lstLanguage.ItemData(varSelected)) = True Then

to

If Me.lstLanguage.ItemData(varSelected) = "Null" Then

(or whatever the value is)

Sorry about the missing parenthesis, btw.

Upon some further testing when I select the Null item by itself nothing
appears on the report, but there are a lot of records which have Null in

[quoted text clipped - 77 lines]

Carma