Multiple Checkboxes to represent choices in filter

[masterminx]

I want to use a form to allow the user to limit the records that are returned
on a larger form. So, the form fields feed criteria in a query. One of the
fields is a status list, which appears as a combo box normally. In order to
allow multiple status selections in the query criteria, I wanted to create a
series of checkboxes for each criteria. However, I'm not sure how to
translate those Boolean values into query critera.

Example: A user checks the box for Completed and the box for Engaged. I
want the query to return records with a Status criteria of Like Completed or
Like Engaged. Is there a better way to do it, short of making a list box
with every possible combination in it? (Which I won't do.)

 

[Klatuu]

Here is a way you can do it. It does require a change to your form, however.
That is, rather than Check Boxes and a Combo box, use one multi select list
box with a row source that includes all the possible status codes. Then, in
the query, use the following function to include only status codes selected
in the list box. Put the call to the function in the Criteria row of your
query for the Status field like this (where [Status] is the name of the field:
SelectStatus([Status])

The function will return true if the status for row is in the list and false
if it does not.

Private Function SelectStatus(strStatusCode As String) As Boolean
Dim varItem As Variant
Dim ctl As Control

'Put the correct name of your form and control here
Set ctl = Forms!MyForm!lstStatusCodes
SelectStatus = False
With ctl
For Each varItem In .ItemsSelected
If strStatusCode = .ItemData(varItem) Then
SelectStatus = True
End If
Next varItem
End With

End Function

 

[masterminx]

Where do I put the function?

Here is a way you can do it. It does require a change to your form, however.
That is, rather than Check Boxes and a Combo box, use one multi select list
box with a row source that includes all the possible status codes. Then, in
the query, use the following function to include only status codes selected
in the list box. Put the call to the function in the Criteria row of your
query for the Status field like this (where [Status] is the name of the field:
SelectStatus([Status])

The function will return true if the status for row is in the list and false
if it does not.

Private Function SelectStatus(strStatusCode As String) As Boolean
Dim varItem As Variant
Dim ctl As Control

'Put the correct name of your form and control here
Set ctl = Forms!MyForm!lstStatusCodes
SelectStatus = False
With ctl
For Each varItem In .ItemsSelected
If strStatusCode = .ItemData(varItem) Then
SelectStatus = True
End If
Next varItem
End With

End Function

I want to use a form to allow the user to limit the records that are returned
on a larger form. So, the form fields feed criteria in a query. One of the
fields is a status list, which appears as a combo box normally. In order to
allow multiple status selections in the query criteria, I wanted to create a
series of checkboxes for each criteria. However, I'm not sure how to
translate those Boolean values into query critera.

Example: A user checks the box for Completed and the box for Engaged. I
want the query to return records with a Status criteria of Like Completed or
Like Engaged. Is there a better way to do it, short of making a list box
with every possible combination in it? (Which I won't do.)

 

[masterminx]

I did that. I made a module called QueryFunction. I get an error that says:
Undefined function in SelectStatus

Am I using it incorrectly?

Put the function in a standard module.

Where do I put the function?

Here is a way you can do it. It does require a change to your form, however.
That is, rather than Check Boxes and a Combo box, use one multi select list
box with a row source that includes all the possible status codes. Then, in
the query, use the following function to include only status codes selected
in the list box. Put the call to the function in the Criteria row of your
query for the Status field like this (where [Status] is the name of the field:
SelectStatus([Status])

The function will return true if the status for row is in the list and false
if it does not.

Private Function SelectStatus(strStatusCode As String) As Boolean
Dim varItem As Variant
Dim ctl As Control

'Put the correct name of your form and control here
Set ctl = Forms!MyForm!lstStatusCodes
SelectStatus = False
With ctl
For Each varItem In .ItemsSelected
If strStatusCode = .ItemData(varItem) Then
SelectStatus = True
End If
Next varItem
End With

End Function


:

I want to use a form to allow the user to limit the records that are returned
on a larger form. So, the form fields feed criteria in a query. One of the
fields is a status list, which appears as a combo box normally. In order to
allow multiple status selections in the query criteria, I wanted to create a
series of checkboxes for each criteria. However, I'm not sure how to
translate those Boolean values into query critera.

Example: A user checks the box for Completed and the box for Engaged. I
want the query to return records with a Status criteria of Like Completed or
Like Engaged. Is there a better way to do it, short of making a list box
with every possible combination in it? (Which I won't do.)

 

[Klatuu]

Put the function in a standard module.

Where do I put the function?

Here is a way you can do it. It does require a change to your form, however.
That is, rather than Check Boxes and a Combo box, use one multi select list
box with a row source that includes all the possible status codes. Then, in
the query, use the following function to include only status codes selected
in the list box. Put the call to the function in the Criteria row of your
query for the Status field like this (where [Status] is the name of the field:
SelectStatus([Status])

The function will return true if the status for row is in the list and false
if it does not.

Private Function SelectStatus(strStatusCode As String) As Boolean
Dim varItem As Variant
Dim ctl As Control

'Put the correct name of your form and control here
Set ctl = Forms!MyForm!lstStatusCodes
SelectStatus = False
With ctl
For Each varItem In .ItemsSelected
If strStatusCode = .ItemData(varItem) Then
SelectStatus = True
End If
Next varItem
End With

End Function

I want to use a form to allow the user to limit the records that are returned
on a larger form. So, the form fields feed criteria in a query. One of the
fields is a status list, which appears as a combo box normally. In order to
allow multiple status selections in the query criteria, I wanted to create a
series of checkboxes for each criteria. However, I'm not sure how to
translate those Boolean values into query critera.

Example: A user checks the box for Completed and the box for Engaged. I
want the query to return records with a Status criteria of Like Completed or
Like Engaged. Is there a better way to do it, short of making a list box
with every possible combination in it? (Which I won't do.)

 

[Douglas J. Steele]

That's because you declared the function as Private. Either remove that
keyword, or change it to Public.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I did that. I made a module called QueryFunction. I get an error that
says:
Undefined function in SelectStatus

Am I using it incorrectly?

Put the function in a standard module.

Where do I put the function?

:

Here is a way you can do it. It does require a change to your form,
however.
That is, rather than Check Boxes and a Combo box, use one multi
select list
box with a row source that includes all the possible status codes.
Then, in
the query, use the following function to include only status codes
selected
in the list box. Put the call to the function in the Criteria row of
your
query for the Status field like this (where [Status] is the name of
the field:
SelectStatus([Status])

The function will return true if the status for row is in the list
and false
if it does not.

Private Function SelectStatus(strStatusCode As String) As Boolean
Dim varItem As Variant
Dim ctl As Control

'Put the correct name of your form and control here
Set ctl = Forms!MyForm!lstStatusCodes
SelectStatus = False
With ctl
For Each varItem In .ItemsSelected
If strStatusCode = .ItemData(varItem) Then
SelectStatus = True
End If
Next varItem
End With

End Function


:

I want to use a form to allow the user to limit the records that
are returned
on a larger form. So, the form fields feed criteria in a query.
One of the
fields is a status list, which appears as a combo box normally. In
order to
allow multiple status selections in the query criteria, I wanted to
create a
series of checkboxes for each criteria. However, I'm not sure how
to
translate those Boolean values into query critera.

Example: A user checks the box for Completed and the box for
Engaged. I
want the query to return records with a Status criteria of Like
Completed or
Like Engaged. Is there a better way to do it, short of making a
list box
with every possible combination in it? (Which I won't do.)

 

[masterminx]

Not to be difficult, but I think I really want to go with the checkboxes.
Could you help me with making a function to replace these IIF statements,
which the query finds too complex? I don't know how to make the list box
give me what I need. Here is an example of what I have:

Like IIf([Forms]![FilterProgManQuick]![Completecheck]=-1,"Completed",0) OR
Like IIf([Forms]![FilterProgManQuick]![Engagedcheck]=-1,"Engaged",0) OR Like
IIf([Forms]![FilterProgManQuick]![Inprocesscheck]=-1,"In Process",0) OR Like
IIf([Forms]![FilterProgManQuick]![Woncheck]=-1,"Won/Pending",0) OR Like IIf
([Forms]![FilterProgManQuick]![Deadcheck]=-1,"Dead/Lost",0)

The intended result is that the Status field in my query translates to
something like this, after the user checks a few boxes and loads the form:

Status: Like "Completed" or Like "Engaged" or Like "In Process".

Please help. I have only a vague idea of how to build the function, and no
idea how to call it from the query.

That's because you declared the function as Private. Either remove that
keyword, or change it to Public.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I did that. I made a module called QueryFunction. I get an error that
says:
Undefined function in SelectStatus

Am I using it incorrectly?

Put the function in a standard module.

:

Where do I put the function?

:

Here is a way you can do it. It does require a change to your form,
however.
That is, rather than Check Boxes and a Combo box, use one multi
select list
box with a row source that includes all the possible status codes.
Then, in
the query, use the following function to include only status codes
selected
in the list box. Put the call to the function in the Criteria row of
your
query for the Status field like this (where [Status] is the name of
the field:
SelectStatus([Status])

The function will return true if the status for row is in the list
and false
if it does not.

Private Function SelectStatus(strStatusCode As String) As Boolean
Dim varItem As Variant
Dim ctl As Control

'Put the correct name of your form and control here
Set ctl = Forms!MyForm!lstStatusCodes
SelectStatus = False
With ctl
For Each varItem In .ItemsSelected
If strStatusCode = .ItemData(varItem) Then
SelectStatus = True
End If
Next varItem
End With

End Function


:

I want to use a form to allow the user to limit the records that
are returned
on a larger form. So, the form fields feed criteria in a query.
One of the
fields is a status list, which appears as a combo box normally. In
order to
allow multiple status selections in the query criteria, I wanted to
create a
series of checkboxes for each criteria. However, I'm not sure how
to
translate those Boolean values into query critera.

Example: A user checks the box for Completed and the box for
Engaged. I
want the query to return records with a Status criteria of Like
Completed or
Like Engaged. Is there a better way to do it, short of making a
list box
with every possible combination in it? (Which I won't do.)

 

[Douglas J Steele]

Since your field is text, you can't using Like 0 if the checkbox isn't
checked. As well, does the field contain simply the word "Completed" or
"Engaged" (or whatever), or are you looking to see whether that word exists
anywhere in the field?

If the field only contains a single word, you don't need the keyword Like.
try

IIf([Forms]![FilterProgManQuick]![Completecheck]=-1,"Completed","") OR
IIf([Forms]![FilterProgManQuick]![Engagedcheck]=-1,"Engaged","") OR
IIf([Forms]![FilterProgManQuick]![Inprocesscheck]=-1,"In Process","") OR
IIf([Forms]![FilterProgManQuick]![Woncheck]=-1,"Won/Pending","") OR
IIf([Forms]![FilterProgManQuick]![Deadcheck]=-1,"Dead/Lost","")

If you're looking for the word anywhere in the field, try:

Like IIf([Forms]![FilterProgManQuick]![Completecheck]=-1,"*Completed*","")
OR Like IIf([Forms]![FilterProgManQuick]![Engagedcheck]=-1,"*Engaged*","")
OR Like IIf([Forms]![FilterProgManQuick]![Inprocesscheck]=-1,"*In
Process*","") OR Like
IIf([Forms]![FilterProgManQuick]![Woncheck]=-1,"*Won/Pending*","") OR Like
IIf([Forms]![FilterProgManQuick]![Deadcheck]=-1,"*Dead/Lost*","")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Not to be difficult, but I think I really want to go with the checkboxes.
Could you help me with making a function to replace these IIF statements,
which the query finds too complex? I don't know how to make the list box
give me what I need. Here is an example of what I have:

Like IIf([Forms]![FilterProgManQuick]![Completecheck]=-1,"Completed",0) OR
Like IIf([Forms]![FilterProgManQuick]![Engagedcheck]=-1,"Engaged",0) OR Like
IIf([Forms]![FilterProgManQuick]![Inprocesscheck]=-1,"In Process",0) OR Like
IIf([Forms]![FilterProgManQuick]![Woncheck]=-1,"Won/Pending",0) OR Like IIf
([Forms]![FilterProgManQuick]![Deadcheck]=-1,"Dead/Lost",0)

The intended result is that the Status field in my query translates to
something like this, after the user checks a few boxes and loads the form:

Status: Like "Completed" or Like "Engaged" or Like "In Process".

Please help. I have only a vague idea of how to build the function, and no
idea how to call it from the query.

That's because you declared the function as Private. Either remove that
keyword, or change it to Public.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I did that. I made a module called QueryFunction. I get an error that
says:
Undefined function in SelectStatus

Am I using it incorrectly?

:

Put the function in a standard module.

:

Where do I put the function?

:

Here is a way you can do it. It does require a change to your form,
however.
That is, rather than Check Boxes and a Combo box, use one multi
select list
box with a row source that includes all the possible status codes.
Then, in
the query, use the following function to include only status codes
selected
in the list box. Put the call to the function in the Criteria row of
your
query for the Status field like this (where [Status] is the name of
the field:
SelectStatus([Status])

The function will return true if the status for row is in the list
and false
if it does not.

Private Function SelectStatus(strStatusCode As String) As Boolean
Dim varItem As Variant
Dim ctl As Control

'Put the correct name of your form and control here
Set ctl = Forms!MyForm!lstStatusCodes
SelectStatus = False
With ctl
For Each varItem In .ItemsSelected
If strStatusCode = .ItemData(varItem) Then
SelectStatus = True
End If
Next varItem
End With

End Function


:

I want to use a form to allow the user to limit the records that
are returned
on a larger form. So, the form fields feed criteria in a query.
One of the
fields is a status list, which appears as a combo box normally. In
order to
allow multiple status selections in the query criteria, I wanted to
create a
series of checkboxes for each criteria. However, I'm not sure how
to
translate those Boolean values into query critera.

Example: A user checks the box for Completed and the box for
Engaged. I
want the query to return records with a Status criteria of Like
Completed or
Like Engaged. Is there a better way to do it, short of making a
list box
with every possible combination in it? (Which I won't do.)

 

[masterminx]

Thank you so much! You are my hero. (Again, I think.)

Since your field is text, you can't using Like 0 if the checkbox isn't
checked. As well, does the field contain simply the word "Completed" or
"Engaged" (or whatever), or are you looking to see whether that word exists
anywhere in the field?

If the field only contains a single word, you don't need the keyword Like.
try

IIf([Forms]![FilterProgManQuick]![Completecheck]=-1,"Completed","") OR
IIf([Forms]![FilterProgManQuick]![Engagedcheck]=-1,"Engaged","") OR
IIf([Forms]![FilterProgManQuick]![Inprocesscheck]=-1,"In Process","") OR
IIf([Forms]![FilterProgManQuick]![Woncheck]=-1,"Won/Pending","") OR
IIf([Forms]![FilterProgManQuick]![Deadcheck]=-1,"Dead/Lost","")

If you're looking for the word anywhere in the field, try:

Like IIf([Forms]![FilterProgManQuick]![Completecheck]=-1,"*Completed*","")
OR Like IIf([Forms]![FilterProgManQuick]![Engagedcheck]=-1,"*Engaged*","")
OR Like IIf([Forms]![FilterProgManQuick]![Inprocesscheck]=-1,"*In
Process*","") OR Like
IIf([Forms]![FilterProgManQuick]![Woncheck]=-1,"*Won/Pending*","") OR Like
IIf([Forms]![FilterProgManQuick]![Deadcheck]=-1,"*Dead/Lost*","")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Not to be difficult, but I think I really want to go with the checkboxes.
Could you help me with making a function to replace these IIF statements,
which the query finds too complex? I don't know how to make the list box
give me what I need. Here is an example of what I have:

Like IIf([Forms]![FilterProgManQuick]![Completecheck]=-1,"Completed",0) OR
Like IIf([Forms]![FilterProgManQuick]![Engagedcheck]=-1,"Engaged",0) OR Like
IIf([Forms]![FilterProgManQuick]![Inprocesscheck]=-1,"In Process",0) OR Like
IIf([Forms]![FilterProgManQuick]![Woncheck]=-1,"Won/Pending",0) OR Like IIf
([Forms]![FilterProgManQuick]![Deadcheck]=-1,"Dead/Lost",0)

The intended result is that the Status field in my query translates to
something like this, after the user checks a few boxes and loads the form:

Status: Like "Completed" or Like "Engaged" or Like "In Process".

Please help. I have only a vague idea of how to build the function, and no
idea how to call it from the query.

That's because you declared the function as Private. Either remove that
keyword, or change it to Public.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I did that. I made a module called QueryFunction. I get an error that
says:
Undefined function in SelectStatus

Am I using it incorrectly?

:

Put the function in a standard module.

:

Where do I put the function?

:

Here is a way you can do it. It does require a change to your form,
however.
That is, rather than Check Boxes and a Combo box, use one multi
select list
box with a row source that includes all the possible status codes.
Then, in
the query, use the following function to include only status codes
selected
in the list box. Put the call to the function in the Criteria row of
your
query for the Status field like this (where [Status] is the name of
the field:
SelectStatus([Status])

The function will return true if the status for row is in the list
and false
if it does not.

Private Function SelectStatus(strStatusCode As String) As Boolean
Dim varItem As Variant
Dim ctl As Control

'Put the correct name of your form and control here
Set ctl = Forms!MyForm!lstStatusCodes
SelectStatus = False
With ctl
For Each varItem In .ItemsSelected
If strStatusCode = .ItemData(varItem) Then
SelectStatus = True
End If
Next varItem
End With

End Function


:

I want to use a form to allow the user to limit the records that
are returned
on a larger form. So, the form fields feed criteria in a query.
One of the
fields is a status list, which appears as a combo box normally. In
order to
allow multiple status selections in the query criteria, I wanted to
create a
series of checkboxes for each criteria. However, I'm not sure how
to
translate those Boolean values into query critera.

Example: A user checks the box for Completed and the box for
Engaged. I
want the query to return records with a Status criteria of Like
Completed or
Like Engaged. Is there a better way to do it, short of making a
list box
with every possible combination in it? (Which I won't do.)

 

[Sandy]

Hello -

How do I apply this strategy if I have several list boxes?

I would like to build a data set that filters on several fields:

If [field1]= null or a or b AND [field2] = null or x or y or z ... etc

where a, b, x, y and z are the items that are selected in the list boxes.

thanks
sandy