Find the closest value if an exact match cannot be found

[mrsbean]

I have a DLookup that finds a customer number in the customer table where the Name of the customer is like a value typed in a text field. The form takes the result of the DLookup and selects a value in a listbox. For example, if I type in the text box "Johns", in the listbox below my text box, the first matching customer name beginning with Johns will be selected ... maybe "Johnson". The listbox contains tens of thousands of names, so a method is needed to jump to a logical place in the list.

What I want to do is to find the next closest value when there is not a matching value. If there are no customers whose names are like (start with) "Johns", then I want to move to the next customer name on the list. In real life, that next name might be "Kempher".

Can anybody point me in the right direction? I have tried doing a different DLookup or a DMin, but I'm not getting there.

MrsBean

 

[PHV]

Any chance you could post your actual code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrsbean]

Okay, here goes ... I'm going from memory because I only connect to this application remotely, and I cannot get to it at the moment. Here is what doesn't work:

Closest = DMin("[Cust No]", "Customer", "[Name] > N'" & Me.Text2 & "'")

I tried inserting a message box before this line

msgbox Me.Text2

It did return the expected value in Me.Text2. It seems that Closest is returning a Null or blank value.

I'm very open to other suggestions how to get to the closest value in the table if this method doesn't work. Thanks.

MrsBean

 

[PHV]

And what about this ?
Closest = DMin("[Cust No]", "Customer", "[Name]>='" & Me!Text2 & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrsbean]

Think I solved the problem, but what I wound up with was a little bit different.

Here is the code that finally worked:

  This = Nz(DLookup("[Cust No]", "Customer", "[Name] like '" & Me.Text2 & "%'"), 0)
    If IsNull(This) Or This = 0 Then
    Dim Closest As String
    Closest = Nz(DMin("[Name]", "Customer", "[Name] > '" & Me.Text2 & "'"), "")
    
    If IsNull(Closest) Or Closest = "" Then
    Exit Sub
    End If
    
    This = Nz(DLookup("[Cust No]", "Customer", "[Name] like '" & Closest & "%'"), 0)
    
    
    If IsNull(This) Or This = 0 Then
    Me.List2 = Null
    Exit Sub
    Else
    Me.List2 = This
    End If
    
    Else
    Me.List2 = This
    End If

Thanks for the input

MrsBean

 

[MichaelRed]

While it is not necessarily popular (perhaps because it isn't trivial?), there is a procedure generically refered to a "Soundex" which was developed specifically for this issue. The 'title' is a deliberate reference to "Sounds Like Index". It was developed by/for government agencies to deal with finding names which sound like (or similar to) each other. There is (should be?) one or more examples in these fora. I believe the routine (within the Ms. A. / VB family is "basSoundex". Search. Please.




MichaelRed