Filter Results

[Nick 'The Database Guy']

Hello,

I am trying to filter results in a query based on values that I have
selected on a form. The only trouble is that Access does not want to
know, saying that the query is to complex, giving me no results or
telling me that I have a type miss match. The thing is that it works
fine if I just specify one value to filter on, but if I choose
multiple values and insert an OR between them it thinks that I am
specifing a text value and encloses the whole thing in quotes.

Below is the code that I am using to take the items that I have
selected from the list box and put them in a single text box.

' Concatenates all the admission types and puts them in the combo box
that the query is looking at.
Public Function FunctAdmission() As Variant
Dim intLoopVar As Integer
With Forms!frmInpatientAnalysis
If .lstAdmissionTypeSelected.ListCount > 0 Then
FunctAdmission = .lstAdmissionTypeSelected.ItemData(0)
For intLoopVar = 1 To .lstAdmissionTypeSelected.ListCount
- 1
FunctAdmission = FunctAdmission & " Or "
& .lstAdmissionTypeSelected.ItemData(intLoopVar)
Next
End If
End With
End Function

I call it with the statement:

Me.txtAdmissionType = FunctAdmission

And the WHERE statement in the SQL looks like this:

WHERE (((tblRDAdmissionType.AdimssionTypeID)=[Forms]!
[frmInpatientAnalysis]![txtAdmissionType]))

I have also tried calling the function straight from the query, which
works on a single value, but again not on multiple values.

Thank you for reading all this and I hope that you can help, you have
my sincerest thanks in advance.

Nick

 

[Allen Browne]

So your function concatenates a list of values together, and you plug the
result into a text box, and then try to read that from the form as a
parameter in the query. No, that's not going to work.

You will need to build the SQL statement dynamically, concatenating the
values into the string. Create a form (continuous or datasheet view if you
want it to look like a query), and apply a filter to it like this:
dim strWhere As String
strWhere = "tblRDAdmissionType.AdimssionTypeID IN (" & FunctAdmission()
& ")"
debug.print strWhere
Me.Filter = strWhere
Me.FilterOn = True

Note that your function will need to separate the values with commas (in
place of the OR), and chop off the last comma. If AdmissionTypeID is a Text
field (not a Number field), you also need quotes as delimiters around the
values. Here's an example of building such a filter string:
http://allenbrowne.com/ser-50.html

 

[Ken Sheridan]

Nick:

You'll find a couple of methods of handling this sort of thing at:


http://support.microsoft.com/kb/100131/en-us


Insert the value list into the control on your form, but as a
comma-separated list not a Boolean expression – that won't work unless you
modify the query's SQL property in code.

The code to fill the text box can go in its AfterUpdate event procedure and
would be:

Dim varItem As Variant
Dim strAmissionTypesList As String
Dim ctrl As Control

Set ctrl = Me.lstAdmissionTypeSelected

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strAmissionTypesList = strAmissionTypesList & "," &
ctrl.ItemData(varItem)
Next varItem

' remove leading comma and insert into text box
Me.txtAdmissionType = Mid(strAmissionTypesList, 2)
End If

If you use method 2 from the above link for instance then the query would be
restricted like so:

WHERE InParam(AdmissionTypeID,
Forms!Forms!frmInpatientAnalysis!txtAdmissionType)

The functions are not lightning fast, especially the first time you open the
query while compiling, but usually acceptable.

BTW even if the data type of the field is text you don't need to wrap each
value in quotes when building the value list. It doesn't seem to work with
dates, though. For that you'd have to use the Instr method. The
disadvantage of the Instr method is that it will also find values which are
substrings of a value entered, so if Jameson as a surname was included in the
parameter list for instance, it would also return anyone with James as a
surname. This can be avoided by including tokens when building the list, but
the InParam function avoids the need for this.

Ken Sheridan
Stafford, England

Hello,

I am trying to filter results in a query based on values that I have
selected on a form. The only trouble is that Access does not want to
know, saying that the query is to complex, giving me no results or
telling me that I have a type miss match. The thing is that it works
fine if I just specify one value to filter on, but if I choose
multiple values and insert an OR between them it thinks that I am
specifing a text value and encloses the whole thing in quotes.

Below is the code that I am using to take the items that I have
selected from the list box and put them in a single text box.

' Concatenates all the admission types and puts them in the combo box
that the query is looking at.
Public Function FunctAdmission() As Variant
Dim intLoopVar As Integer
With Forms!frmInpatientAnalysis
If .lstAdmissionTypeSelected.ListCount > 0 Then
FunctAdmission = .lstAdmissionTypeSelected.ItemData(0)
For intLoopVar = 1 To .lstAdmissionTypeSelected.ListCount
- 1
FunctAdmission = FunctAdmission & " Or "
& .lstAdmissionTypeSelected.ItemData(intLoopVar)
Next
End If
End With
End Function

I call it with the statement:

Me.txtAdmissionType = FunctAdmission

And the WHERE statement in the SQL looks like this:

WHERE (((tblRDAdmissionType.AdimssionTypeID)=[Forms]!
[frmInpatientAnalysis]![txtAdmissionType]))

I have also tried calling the function straight from the query, which
works on a single value, but again not on multiple values.

Thank you for reading all this and I hope that you can help, you have
my sincerest thanks in advance.

Nick

 

[Nick 'The Database Guy']

Nick:

You'll find a couple of methods of handling this sort of thing at:

http://support.microsoft.com/kb/100131/en-us

Insert the value list into the control on your form, but as a
comma-separated list not a Boolean expression – that won't work unless you
modify the query's SQL property in code.

The code to fill the text box can go in its AfterUpdate event procedure and
would be:

    Dim varItem As Variant
    Dim strAmissionTypesList As String
    Dim ctrl As Control

    Set ctrl = Me.lstAdmissionTypeSelected

    ' loop through list box's ItemsSelected collection
    ' and build comma separated list of selected items
    If ctrl.ItemsSelected.Count > 0 Then
        For Each varItem In ctrl.ItemsSelected
            strAmissionTypesList = strAmissionTypesList & "," &
ctrl.ItemData(varItem)
        Next varItem

        ' remove leading comma and insert into text box
        Me.txtAdmissionType = Mid(strAmissionTypesList, 2)
    End If

If you use method 2 from the above link for instance then the query wouldbe
restricted like so:

WHERE InParam(AdmissionTypeID,
Forms!Forms!frmInpatientAnalysis!txtAdmissionType)

The functions are not lightning fast, especially the first time you open the
query while compiling, but usually acceptable.

BTW even if the data type of the field is text you don't need to wrap each
value in quotes when building the value list.  It doesn't seem to work with
dates, though.  For that you'd have to use the Instr method.  The
disadvantage of the Instr method is that it will also find values which are
substrings of a value entered, so if Jameson as a surname was included inthe
parameter list for instance, it would also return anyone with James as a
surname.  This can be avoided by including tokens when building the list, but
the InParam function avoids the need for this.

Ken Sheridan
Stafford, England

:

Hello,

I am trying to filter results in a query based on values that I have
selected on a form.  The only trouble is that Access does not want to
know, saying that the query is to complex, giving me no results or
telling me that I have a type miss match.  The thing is that it works
fine if I just specify one value to filter on, but if I choose
multiple values and insert an OR between them it thinks that I am
specifing a text value and encloses the whole thing in quotes.

Below is the code that I am using to take the items that I have
selected from the list box and put them in a single text box.

' Concatenates all the admission types and puts them in the combo box
that the query is looking at.
Public Function FunctAdmission() As Variant
    Dim intLoopVar As Integer
    With Forms!frmInpatientAnalysis
        If .lstAdmissionTypeSelected.ListCount > 0 Then
            FunctAdmission = .lstAdmissionTypeSelected.ItemData(0)
            For intLoopVar = 1 To .lstAdmissionTypeSelected.ListCount
- 1
                FunctAdmission = FunctAdmission & " Or "
& .lstAdmissionTypeSelected.ItemData(intLoopVar)
            Next
        End If
    End With
End Function

I call it with the statement:

Me.txtAdmissionType = FunctAdmission

And the WHERE statement in the SQL looks like this:

WHERE (((tblRDAdmissionType.AdimssionTypeID)=[Forms]!
[frmInpatientAnalysis]![txtAdmissionType]))

I have also tried calling the function straight from the query, which
works on a single value, but again not on multiple values.

Thank you for reading all this and I hope that you can help, you have
my sincerest thanks in advance.

Nick- Hide quoted text -

- Show quoted text -

Thank you both for your replies, but particularly Ken who showed me
exactly what I wanted to do. I realise that I should have worked it
out myself now that I see the answer, but itn't that always the way?
Any way thanks again, and have a great day.

Nick

 

[Nick 'The Database Guy']

Nick:

You'll find a couple of methods of handling this sort of thing at:

Insert the value list into the control on your form, but as a
comma-separated list not a Boolean expression – that won't work unless you
modify the query's SQL property in code.

The code to fill the text box can go in its AfterUpdate event procedureand
would be:

    Dim varItem As Variant
    Dim strAmissionTypesList As String
    Dim ctrl As Control

    Set ctrl = Me.lstAdmissionTypeSelected

    ' loop through list box's ItemsSelected collection
    ' and build comma separated list of selected items
    If ctrl.ItemsSelected.Count > 0 Then
        For Each varItem In ctrl.ItemsSelected
            strAmissionTypesList = strAmissionTypesList &"," &
ctrl.ItemData(varItem)
        Next varItem

        ' remove leading comma and insert into text box
        Me.txtAdmissionType = Mid(strAmissionTypesList, 2)
    End If

If you use method 2 from the above link for instance then the query would be
restricted like so:

WHERE InParam(AdmissionTypeID,
Forms!Forms!frmInpatientAnalysis!txtAdmissionType)

The functions are not lightning fast, especially the first time you open the
query while compiling, but usually acceptable.

BTW even if the data type of the field is text you don't need to wrap each
value in quotes when building the value list.  It doesn't seem to work with
dates, though.  For that you'd have to use the Instr method.  The
disadvantage of the Instr method is that it will also find values whichare
substrings of a value entered, so if Jameson as a surname was included in the
parameter list for instance, it would also return anyone with James as a
surname.  This can be avoided by including tokens when building the list, but
the InParam function avoids the need for this.

Ken Sheridan
Stafford, England

Hello,
I am trying to filter results in a query based on values that I have
selected on a form.  The only trouble is that Access does not want to
know, saying that the query is to complex, giving me no results or
telling me that I have a type miss match.  The thing is that it works
fine if I just specify one value to filter on, but if I choose
multiple values and insert an OR between them it thinks that I am
specifing a text value and encloses the whole thing in quotes.
Below is the code that I am using to take the items that I have
selected from the list box and put them in a single text box.
' Concatenates all the admission types and puts them in the combo box
that the query is looking at.
Public Function FunctAdmission() As Variant
    Dim intLoopVar As Integer
    With Forms!frmInpatientAnalysis
        If .lstAdmissionTypeSelected.ListCount > 0 Then
            FunctAdmission = .lstAdmissionTypeSelected.ItemData(0)
            For intLoopVar = 1 To .lstAdmissionTypeSelected.ListCount
- 1
                FunctAdmission = FunctAdmission & "Or "
& .lstAdmissionTypeSelected.ItemData(intLoopVar)
            Next
        End If
    End With
End Function
I call it with the statement:
Me.txtAdmissionType = FunctAdmission
And the WHERE statement in the SQL looks like this:
WHERE (((tblRDAdmissionType.AdimssionTypeID)=[Forms]!
[frmInpatientAnalysis]![txtAdmissionType]))
I have also tried calling the function straight from the query, which
works on a single value, but again not on multiple values.
Thank you for reading all this and I hope that you can help, you have
my sincerest thanks in advance.
Nick- Hide quoted text -

- Show quoted text -

Thank you both for your replies, but particularly Ken who showed me
exactly what I wanted to do.  I realise that I should have worked it
out myself now that I see the answer, but itn't that always the way?
Any way thanks again, and have a great day.

Nick- Hide quoted text -

- Show quoted text -

* isn't