Returning a list of keywords based on partial matches (1 Viewer)

Alc

Registered User.
Local time
Today, 12:59
Joined
Mar 23, 2007
Messages
2,407
I have a table of criteria, one field of which contains part numbers, these can be any length from 1 character up.
The lower the number of characters, the broader the range of items the part applies to. For example:
  • 12345 might be a specific part from a machine
  • Parts starting with 1234 might mean all parts in the left, front, top side of a machine
  • Parts starting with 123 might mean all parts in the left, front side of the machine
  • Parts starting with 12 might mean all parts in the left side of the machine
  • Parts starting with 1 might mean all parts in the machine
I have a form where the user enters various values and VBA which then creates a SQL query to look for any records in the criteria table that match what was entered.
This was created before my time and just looks for exact matches on part numbers.

Where I'm getting stuck is adapting it to include partial part matches. For example:
User enters 12345. I need to return any parts starting with 1, 12, 123, 1234 and 12345 but not 12346, 12347, etc.

I'm trying to picture a way to take into account the length of the entered value when checking for matches.
Something like "If the length = 5, check for a match on the first 4 characters but exclude anything where the full 5 don't match, then add those where the first three match but exclude anything where the first four don't, and so on"

This feels like it should be easy and, I'm sure, many will read it and wonder why it's even a problem, however it has me stuck.
 

Minty

AWF VIP
Local time
Today, 16:59
Joined
Jul 26, 2013
Messages
10,354
This sounds like an ideal use of a select case statement - AIr Code but something like

SQL:
Select Case Len(Me.SeacrhField)

    Case 5
        Searchcode for the whole thing
    Case 4
        Search Cdoe or where clause for most things
    Case 3
        etc etc
        
    case 2
        ...
    Case 1
        ...
End SELECT
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:59
Joined
Jul 9, 2003
Messages
16,244
I have a product called "Nifty Search Form" which I think may be of use to you. You can see a video of it working here:-

Nifty Access Search Form DEMO​


And there's more information about it on my website here:-


You are welcome to a free copy of the "Nifty Search Form".
If you want a free copy, contact me and I will explain how you can obtain it for free.
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:59
Joined
Jan 23, 2006
Messages
15,364
@Alc

Can you show us the tables and relationships for your database?
Many coding systems are hierarchies where different levels have different meanings.
 

Alc

Registered User.
Local time
Today, 12:59
Joined
Mar 23, 2007
Messages
2,407
Thanks for the replies
As is often the case, writing it out clarified some ideas for me and it's now working.

The problem was caused by using LIKE to do the matches on the sorter part numbers.
In those cases, I should have been comparing each to the section of the entered value using '='.
For example, user enters "12345" I should have compared the left character using = "1" not LIKE "1*", the two left characters against = "12" not LIKE "12*", and so on.

Thanks again.
 

Users who are viewing this thread

Top Bottom