single business name in search list box ? Query Type?

[d9pierce1]

Sorry Orange, some how the filter call out didnt take in the after update event. So sorry!
It works just fine.
Will mark as solved. Again, thank you so much!
Major help here

[orange]

You have 2 events.

Private Sub FilterPhone_AfterUpdate()
Call bListBoxFilter
End Sub

Private Sub FilterPhoneNumber_AfterUpdate()

End Sub

The second refers to the name of the textbox on the form???

OK just saw your post.
Glad you have it working

[Welshgasman]

Phone number seems to work for me.?
I put in 972 and it finds the parkway with the 972 number?, same with 221 ?

You need to walk through your code with F8 and see what is produced.
Use breakpoints to set a good place to start/stop.

[d9pierce1]

Hi all,
One final question if I may,

On my primary form, when I type in the search criteria boxes, such as Business Name, Lets say I type P into that, I get two records of same company showing in listbox. Is
there a way to make this search criteria return a DISTINCT value in that list box from my modulle "ContactSearch" to only show one business per the same company if mutipule entities or phone numbers... I have tried many methods and cannot
find a way to make that return a DISTINCT in the list box. I understand that parkway has two entities but I would like it so that it would show up as only one business if that is possible?

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"

    'chop off the last 5 character
    lngLen = Len(strWhere) - 5
    If lngLen > 0 Then    '<<-- if  Len of strWHERE is > 0, then there must be a filter to apply
        strWhere = Left(strWhere, lngLen)
        strWhere = " WHERE " & strWhere  ' add "WHERE to the filter string (strWHERE)


Forms!frmBusiness.LstBusinessSearch.RowSource = b & strWhere

[orange]

Dave,

I think this is basically what I found when dealing with the RESET click event.

Your CONST b has too many fields to give you 1 single Business Name.

You can prove/disprove/confirm things if:

You take the SQL related to your Const b, using the query designer in sql view, add the SQL
try getting single records via Distinct.

Let us know what you find.

You can use a different construct:

select Distinct BusinessName
from
(SELECT 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)


Not sure if you can adapt something like the following, but to show a technique based on your CONST b:

Sub jack()
Dim az As String
az = "BusinessName"
Dim msql As String
msql = " Select Distinct " & az & " From (" & b & ")"
Debug.Print msql
End Sub

which results in

Select Distinct BusinessName From (SELECT 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)

[d9pierce1]

Hi Orange,
Yes, I did that on my desktop copy and it didnt make a difference.
I have tried a lot of things, but as you say I have to many fields to distinct it...
I would think there would be some way to distinct this as most anything is possible!
Maybe I will try multiple Querys and see what that does?
I dont know...
Thanks again,
Dave

[orange]

I have updated my previous post.
Take a look.

[d9pierce1]

Hi Orange,
The first part was easy. The (Below) is stumping me.
Where would this go? On Form as Sub jack()? or in the Mod?
I assume there must be some way of calling on it?
Way over my head here....

Dave,


Not sure if you can adapt something like the following, but to show a technique based on your CONST b:

Sub jack()
Dim az As String
az = "BusinessName"
Dim msql As String
msql = " Select Distinct " & az & " From (" & b & ")"
Debug.Print msql
End Sub

[orange]

Dave,

It does NOT go anywhere.
It was my attempt to show that you could continue to reference your CONST b, but you would have to provide some means to restrict the number of fields over which the DISTINCT would apply.

An analogy (a stretch at best but maybe gets the point across)

Your b is similar to get me all cars; my constraint is like get me only cars from Oklahoma.

Specific to your #21, there are so many fields in your CONST b, that in order to get DISTINCT records, the BusinessName will be repeated. To get Distinct values for the fields you need, you have to remove those fields which are of no interest to you. If you need Distinct BusinessName, you don't need city, state, entity type, county.... etc. It is these unneeded fields that are resulting in duplicate BusinessName.

[d9pierce1]

thanks Orange,
Sorry for all of this. If I remove all the City, County.... then I cant search for them. I do understand now what you were doing with the Sub jack().

I guess my best question here is are there better means of a search that I can search for all these things but yet only show the one company related to the search?
If not in the query, then it wont pull it up will it?

[d9pierce1]

I got it! I finally got it. I had to add a few fields to the Select Distinct and change the line up but it works!
Wow, Thank you Oraange. I will call this solved!!!!!!!
Here it is!

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

[orange]

Dave,

The bottom line is that CONST b has too many fields as it stands to provide unique BusinessName.

My Sub Jack example shows that you could set up code that would reduce the number of fields involved when you need DISTINCT values of specific fields.

You could adjust the code when you are searching for DISTINCT values to reflect the field/fields involved.

For example, if you wanted to search by Phone, you could set up something like a function

Function Jack2(sfield1 as string,Optional sfield2 as string ="") as string
Dim az As String
az = sField1
Dim msql As String
msql = " Select Distinct " & az & " From (" & b & ")"
Jack2 = msql
end function

Here, if you were searching for Phone, you might use
Jack2 "Phone"
to produce the revised SQL to get a list of Distinct Phone numbers.

If you required 2 fields (and you could expand to more)

Jack2 "County","State" to produce the revised SQL to get a list of unique County, State

You would have to see how this would fit with you use of Filters. It's all related.


The CONST b is a sound approach, but it includes too many fields to get unique/distinct values of some subset of fields. Thus, you need to restrict the SQL represented by b when you are looking for Distinct values.