single business name in search list box ? Query Type?

[d9pierce1]

Views: 16 Size: 1.80 MB">Profile_Backup.zip

Hi all,
Two questions please!

First off, I have a list box for search on primary form. Is it possible to only show one business name instead of multiples of each entity for that business?
Currently it shows multi names of business name as there are more then one entity. What i am looking for is to only show one business name (Distinct)? when a company
has more then one entity or phone number..... I understand that its only showing what my query is showing but not real sure what I need to do to force just one business name
into list box?

Second, I need some help with the Phone # search criteria as its not filtering out anything. Rest of the fields are good. Not sure if I need some # in the code line in module or what?

Thanks
Dave

[orange]

Which name would you like? Even distinct will get you 2 records.

[d9pierce1]

Hi Orange,
I want each business name listed but only once.
Sorry for delayed response as I had an emergency come up

Thanks

[orange]

d9Pierce1,
I know what you are asking. My point is that your query does not return a single record.
So, in business terms, what exactly, uniquely identifies the "business name" you want to return/list?

If you add a DISTINCT in your query-for the example in your database- you get 2 records. And you only want 1. Which 1 do you want in your example? That is the 'business rule' I am asking about.

[d9pierce1]

Orange,
I want each company to show up in list box. I just dont want more then one company name of the same. As of now for this test i have two records of Business Names. Parkway and David Harris.
In the list box i have Parkway twice, david harris once. I just want the list box to show one Parkway and one David Harris, Not 2 Parkway and one David Harris

[d9pierce1]

Orange, yes, that worked just fine adding Distinct to the row source.
Thanks, Sorry about confusion.
However, when I hit reset button, it goes back to not being distinct? I tried adding a Distinct to my select in module but didnt help

Any ideas on the phone Number search criteria?
Dave

[orange]

Dave,

In your database, go to the design of form frmBusiness.
Click on LstBusinessSearch.
In the properties window, under Data, then in Rowsource, add Distinct as follows>

SELECT DISTINCT [qryBusinessSearch].[BusinessID]
, [qryBusinessSearch].[BusinessName], [qryBusinessSearch].[CategoryID]
, [qryBusinessSearch].[SubCategoryID], [qryBusinessSearch].[CategoryName]
, [qryBusinessSearch].[SubCategoryName] FROM qryBusinessSearch 
ORDER BY [BusinessName];

Then switch to Form view.

ta! Daaa!

[d9pierce1]

Thanks Orange,

I did that and worked well except for when I hit reset it shows up as 2 Parkways and one Harris again. I tried adding select distinct to the module but didnt help
Thanks

[orange]

Dave,

What is the Reset button? What does it do?

I tried adding select distinct to the module but didnt help

What module? It's criteria within your query SQL.

[d9pierce1]

Orange
The reset button is to clear all the search fields. The Module is ContactSearch what filters what is in the boxes below the list box

[d9pierce1]

Orange, Reset code, and The module is in the Contact Search Module,

Public Const b As String = "SELECT DISTINCT BusinessID, BusinessName, CategoryID, SubCategoryID, CategoryName, SubCategoryName, DBA,  CategoryEntityID, BusinessEntityID, BusinessAddressID, AddressTypeID, cboDataValue, Address, City, State, ZipCode, County, Reference, EntityReference, Active, Preferred, Solicit, BusinessPhoneID, PhoneNumber From qryBusinessSearch"

Private Sub CmdReset_Click()
'Purpose:   Clear all the search boxes in the Form , and show all records again.
    Dim ctl As Control


    'Clear all the controls in the Form header section.
    '    For Each ctl In Me.Section(acHeader).Controls
    '        Select Case ctl.ControlType
    '        Case acTextBox, acComboBox, acOptionGroup
    '            ctl = Null
    '        Case acCheckBox
    '            ctl.Value = False
    '        End Select
    '    Next


    'an alternative to FOR EACH.... NEXT loop
    ' only a few controls so just set the values directly
    Me.FilterBusinessName = vbNullString
    Me.FilterDBA = vbNullString
    Me.FilterAddress = vbNullString
    Me.FilterReference = vbNullString
    Me.FilterPhoneNumber = vbNullString
    Me.cboFilterCity = Null
    Me.cboFilterState = Null
    Me.cboFilterZip = Null
    Me.cboFilterCounty = Null
    Me.cboFilterIndustry = Null
    Me.cboFilterCategory = Null
    Me.cboFilterEntity = Null
    Me.FilterIsActive = 0
    Me.FilterIsPreferred = 0
    Me.FilterNoSolicit = 0
    Me.LblBusinessAvailable.Caption = "Business's Avaliable"
    ' now set the listbox back to default
    Me.LstBusinessSearch.RowSource = b
End Sub

[orange]

Dave,

In the reset event click, you are resetting the listbox rowsource to b.
and b is on CONST

Public Const b As String = "SELECT DISTINCT BusinessID, BusinessName, CategoryID, SubCategoryID, CategoryName, SubCategoryName, DBA,  CategoryEntityID, BusinessEntityID, BusinessAddressID, AddressTypeID, cboDataValue, Address, City, State, ZipCode, County, Reference, EntityReference, Active, Preferred, Solicit, BusinessPhoneID, PhoneNumber From qryBusinessSearch"

where as the Rowsource for the list in the Property Data is

SELECT DISTINCT qryBusinessSearch.BusinessID, qryBusinessSearch.BusinessName, qryBusinessSearch.CategoryID, qryBusinessSearch.SubCategoryID, qryBusinessSearch.CategoryName, qryBusinessSearch.SubCategoryName
FROM qryBusinessSearch
ORDER BY qryBusinessSearch.[BusinessName];

These are not the same.

When you reset, you should be using the rowsource with the DISTINCT if you want unique records.

I suggest you revise part of the RESET code to the following:

' now set the listbox back to default
' Me.LstBusinessSearch.RowSource = b '<<<<Comment this line
'
'This is the replacement to provide the Distinct values after hitting reset.

    Me.LstBusinessSearch.RowSource = "SELECT DISTINCT qryBusinessSearch.BusinessID, " _
     &  "qryBusinessSearch.BusinessName," _
     & "qryBusinessSearch.CategoryID, qryBusinessSearch.SubCategoryID," _
     & "qryBusinessSearch.CategoryName," _
     & "qryBusinessSearch.SubCategoryName" _
     & " FROM qryBusinessSearch " _
     & " ORDER BY qryBusinessSearch.[BusinessName];"

Works on my copy of your database.

[d9pierce1]

Thank you Orange,
Have a great night!
That worked well for me. I learned a few things also so that's always great.
Now if I can figure out why my Phone # Search doesn't work I will be set
Again,
Thank you
Dave

[orange]

Dave,

What exactly is the issue with Phone # Search?
Where is it and how do you invoke it?
What do you expect to happen?

[d9pierce1]

The phone # is on primary form under list box with many different search fields. When I type in a phone number or part of one I would expect
it to show the business with that number or like that number? All the search criteria works great except for phone #
Its in the module but I may need some # ? Not sure where to even put them. PhoneNumber is in a txt box so whould be text?

Currently it does not filter the phone number at all
Thanks