Filtering search results

[Guest]

I have a custom search form that returns results in a subform. I don't know
if this is possible but how can I display just 1 record even if multiple
search results match it? Here is a snippet of the code that creates the
where statement and opens the subform filter:

Private Sub btnSearch_Click()
Dim strWhere As String

If Not IsNull(Me.txtCaseNum) Then
strWhere = strWhere & "tblCasesMain.CaseNum LIKE '*" & Me.txtCaseNum & "*'"
End If
'----------
If Not IsNull(Me.txtFirstName) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNameslst.FirstName LIKE '*" & Me.txtFirstName & "*'"
End If
'----------
If Not IsNull(Me.curAmount) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCasesMain.Exposure = " & Me.curAmount & " OR " &
"tblBalances.InitBal= " & Me.curAmount & " OR " &
"tblPaymentslst.PaymentAmount= " & Me.curAmount
Debug.Print strWhere
End If

If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
With Me.sbfBrowse.Form
.Filter = strWhere
.FilterOn = True
End With
End Sub

 

[Wolfgang Kais]

Hello Underpaidadmin.

I have a custom search form that returns results in a subform.
I don't know if this is possible but how can I display just 1 record
even if multiple search results match it? [... ]

How about changing the DefaultView property of the subform?

 

[Guest]

As far as I can see changing the Defaultview property just changes 'look' of
the output. I want to actually filter out certain records from displaying if
certain fields match. In fact I have just recently learned about the
DISTINCT property of SQL and was hoping someone could perhaps help me
understand how this could work to solve my problem.

Hello Underpaidadmin.

I have a custom search form that returns results in a subform.
I don't know if this is possible but how can I display just 1 record
even if multiple search results match it? [... ]

How about changing the DefaultView property of the subform?

 

[Wolfgang Kais]

Hello "Underpaidadmin"

I have a custom search form that returns results in a subform.
I don't know if this is possible but how can I display just 1 record
even if multiple search results match it? [... ]

How about changing the DefaultView property of the subform?

As far as I can see changing the Defaultview property just changes
'look' of the output. I want to actually filter out certain records from
displaying if certain fields match. In fact I have just recently learned
about the DISTINCT property of SQL and was hoping someone could
perhaps help me understand how this could work to solve my problem.

The question is: How do you filter in the subform?
You can of course bas the subform on a query that does not contain the
primary key of the table the data is based on and ise the HideDuplicates
property of that query (this will create a "sekect distinct" query). If all
matching records contain the same data, this will show only one matching
record.
If your subform is not linked to the master form, you could specify a
"select top 1" query as the recordsource of the subform in the click event
for a filter button, including the filter criteria, this will result in only
the first matching record.

 

[Guest]

My subform is in the form footer section of the main form. It is based off a
query. The main form gets the information the user wants to search for
creates a where statement and applies it as a filter to the subform in the
form footer section. Hmm I can't find this HideDuplicates setting anywhere.
It doesn't mention anything about queries having a HideDuplicates property am
I not looking in the right place?

Hello "Underpaidadmin"

I have a custom search form that returns results in a subform.
I don't know if this is possible but how can I display just 1 record
even if multiple search results match it? [... ]
How about changing the DefaultView property of the subform?

As far as I can see changing the Defaultview property just changes
'look' of the output. I want to actually filter out certain records from
displaying if certain fields match. In fact I have just recently learned
about the DISTINCT property of SQL and was hoping someone could
perhaps help me understand how this could work to solve my problem.

The question is: How do you filter in the subform?
You can of course bas the subform on a query that does not contain the
primary key of the table the data is based on and ise the HideDuplicates
property of that query (this will create a "sekect distinct" query). If all
matching records contain the same data, this will show only one matching
record.
If your subform is not linked to the master form, you could specify a
"select top 1" query as the recordsource of the subform in the click event
for a filter button, including the filter criteria, this will result in only
the first matching record.

 

[Guest]

Ok it is called UniqueValues or UniqueRecords properties. Neither of these
changed my results in any way so perhaps I best explain the mechanics of my
search form better. The main form is unbound. The user types in or selects
various criteria from list boxes. When they hit the Search button it takes
the information they provided and makes a where statement:

If Not IsNull(Me.curAmount) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCasesMain.Exposure = " & Me.curAmount & " OR " &
"tblBalances.InitBal= " & Me.curAmount & " OR " &
"tblPaymentslst.PaymentAmount= " & Me.curAmount
End If

It uses this where statement to set the filter of the subform located in the
footer section. This subform is based on a query that draws all records from
the 8 linked tables. So when if all fields are empty the subform displays
all records. If they are filled in they display matching records. My
problem comes when I have a record in one table that links to multiple
records in another table, I get the same record listed several times. In
some cases I want this to happen, such as when a user searches a first, last,
or company name. However if they are searching for payments as well they do
not want to see 5 of the same records if there are only two names linked to
it.

I felt like I didn't explain it very well before which is why I wasn't
understanding the answers. I apologize for that and thank you for persisting
with me.

My subform is in the form footer section of the main form. It is based off a
query. The main form gets the information the user wants to search for
creates a where statement and applies it as a filter to the subform in the
form footer section. Hmm I can't find this HideDuplicates setting anywhere.
It doesn't mention anything about queries having a HideDuplicates property am
I not looking in the right place?

Hello "Underpaidadmin"

I have a custom search form that returns results in a subform.
I don't know if this is possible but how can I display just 1 record
even if multiple search results match it? [... ]

How about changing the DefaultView property of the subform?

As far as I can see changing the Defaultview property just changes
'look' of the output. I want to actually filter out certain records from
displaying if certain fields match. In fact I have just recently learned
about the DISTINCT property of SQL and was hoping someone could
perhaps help me understand how this could work to solve my problem.

The question is: How do you filter in the subform?
You can of course bas the subform on a query that does not contain the
primary key of the table the data is based on and ise the HideDuplicates
property of that query (this will create a "sekect distinct" query). If all
matching records contain the same data, this will show only one matching
record.
If your subform is not linked to the master form, you could specify a
"select top 1" query as the recordsource of the subform in the click event
for a filter button, including the filter criteria, this will result in only
the first matching record.

 

[Wolfgang Kais]

Hello "Underpaidadmin".

I have a custom search form that returns results in a subform.
I don't know if this is possible but how can I display just 1
record even if multiple search results match it? [... ]
How about changing the DefaultView property of the subform?
As far as I can see changing the Defaultview property just
changes 'look' of the output. I want to actually filter out
certain records from displaying if certain fields match.
In fact I have just recently learned about the DISTINCT
property of SQL and was hoping someone could perhaps help
me understand how this could work to solve my problem.

The question is: How do you filter in the subform?
You can of course base the subform on a query that does not
contain the primary key of the table the data is based on and
use the HideDuplicates property of that query (this will create
a "select distinct" query). If all matching records contain the
same data, this will show only one matching record.
If your subform is not linked to the master form, you could
specify a "select top 1" query as the recordsource of the subform
in the click event for a filter button, including the filter
criteria, this will result in only the first matching record.

My subform is in the form footer section of the main form. It
is based off a query. The main form gets the information the user
wants to search for creates a where statement and applies it as a
filter to the subform in the form footer section.
Hmm I can't find this HideDuplicates setting anywhere.
It doesn't mention anything about queries having a HideDuplicates
property am I not looking in the right place?

Ok it is called UniqueValues or UniqueRecords properties.

Sorry for that.

Neither of these changed my results in any way so perhaps I best
explain the mechanics of my search form better. The main form is
unbound. The user types in or selects various criteria from list
boxes. When they hit the Search button it takes the information
they provided and makes a where statement:

If Not IsNull(Me.curAmount) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCasesMain.Exposure = " & Me.curAmount &
" OR " & "tblBalances.InitBal= " & Me.curAmount & " OR " &
"tblPaymentslst.PaymentAmount= " & Me.curAmount
End If

It uses this where statement to set the filter of the subform
located in the footer section. This subform is based on a query that
draws all records from the 8 linked tables. So when if all fields
are empty the subform displays all records. If they are filled in
they display matching records. My problem comes when I have a record
in one table that links to multiple records in another table, I get
the same record listed several times. In some cases I want this to
happen, such as when a user searches a first, last, or company name.
However if they are searching for payments as well they do not want
to see 5 of the same records if there are only two names linked to
it.

I felt like I didn't explain it very well before which is why I
wasn't understanding the answers. I apologize for that and thank you
for persisting with me.

Sorry for using the wrong property name.
UniqueValues corresponds to DISTINCT in the query.
Using a filter you can't hide duplicate values or just display the
first one (sometimes).
I think the problem is the query, not the filter.
Be sure the the query doesn't contain any field that is not necessary
for your form and use the UniqueValues property (distinct).
If that doesn't solve the problem, you will probably have to change
the record source property (not the filter) of the subform depending
on the criteria provided by the user.

 

[Guest]

Got it working for the most part. There are still some issues with one of
the files still showing up multiple times, but I won't worry over the spilled
milk for now. Going to move on to other issues. Sorry for the poor
explainations, don't usually know the right words to describe many of the
features so I kind of wing it.

Thank you so much for sticking with me to help me fix this.

Hello "Underpaidadmin".

I have a custom search form that returns results in a subform.
I don't know if this is possible but how can I display just 1
record even if multiple search results match it? [... ]
How about changing the DefaultView property of the subform?
As far as I can see changing the Defaultview property just
changes 'look' of the output. I want to actually filter out
certain records from displaying if certain fields match.
In fact I have just recently learned about the DISTINCT
property of SQL and was hoping someone could perhaps help
me understand how this could work to solve my problem.
The question is: How do you filter in the subform?
You can of course base the subform on a query that does not
contain the primary key of the table the data is based on and
use the HideDuplicates property of that query (this will create
a "select distinct" query). If all matching records contain the
same data, this will show only one matching record.
If your subform is not linked to the master form, you could
specify a "select top 1" query as the recordsource of the subform
in the click event for a filter button, including the filter
criteria, this will result in only the first matching record.

My subform is in the form footer section of the main form. It
is based off a query. The main form gets the information the user
wants to search for creates a where statement and applies it as a
filter to the subform in the form footer section.
Hmm I can't find this HideDuplicates setting anywhere.
It doesn't mention anything about queries having a HideDuplicates
property am I not looking in the right place?

Ok it is called UniqueValues or UniqueRecords properties.

Sorry for that.

Neither of these changed my results in any way so perhaps I best
explain the mechanics of my search form better. The main form is
unbound. The user types in or selects various criteria from list
boxes. When they hit the Search button it takes the information
they provided and makes a where statement:

If Not IsNull(Me.curAmount) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCasesMain.Exposure = " & Me.curAmount &
" OR " & "tblBalances.InitBal= " & Me.curAmount & " OR " &
"tblPaymentslst.PaymentAmount= " & Me.curAmount
End If

It uses this where statement to set the filter of the subform
located in the footer section. This subform is based on a query that
draws all records from the 8 linked tables. So when if all fields
are empty the subform displays all records. If they are filled in
they display matching records. My problem comes when I have a record
in one table that links to multiple records in another table, I get
the same record listed several times. In some cases I want this to
happen, such as when a user searches a first, last, or company name.
However if they are searching for payments as well they do not want
to see 5 of the same records if there are only two names linked to
it.

I felt like I didn't explain it very well before which is why I
wasn't understanding the answers. I apologize for that and thank you
for persisting with me.

Sorry for using the wrong property name.
UniqueValues corresponds to DISTINCT in the query.
Using a filter you can't hide duplicate values or just display the
first one (sometimes).
I think the problem is the query, not the filter.
Be sure the the query doesn't contain any field that is not necessary
for your form and use the UniqueValues property (distinct).
If that doesn't solve the problem, you will probably have to change
the record source property (not the filter) of the subform depending
on the criteria provided by the user.