How to Find Similar Strings Ignoring Hyphens

[doyle60]

Some users put in a style with a hyphen (7700-23) and others without
(770023). I want a query to find styles that contain hyphens but only
if that style was also entered without the hyphen.

Access: Access 97
The table: OrderDetails
The field: Style

Fields to return (also in table OrderDetails):
Style
PO
Counter

I could build a queery chain to do this, but Access 97 does not seem
to have the Replace function.

Matt

 

[Marshall Barton]

Some users put in a style with a hyphen (7700-23) and others without
(770023). I want a query to find styles that contain hyphens but only
if that style was also entered without the hyphen.

Access: Access 97
The table: OrderDetails
The field: Style

Fields to return (also in table OrderDetails):
Style
PO
Counter

I could build a queery chain to do this, but Access 97 does not seem
to have the Replace function.

You don't need the Replace function.

Try using a query like:

SELECT T.Style, T.PO, T.Counter, X.Style
FROM OrderDetails As T INNER JOIN OrderDetails As X
ON T.PO = X.PO And T.Counter = X.Counter
WHERE InStr(T.Style, "-") = 0
And InStr(X.Style, "-") > 0
And T.Style = Left(X.Style, InStr(X.Style, "-") - 1) &
Mid(X.Style, InStr(X.Style, "-") + 1)

That's probably not quite right because I don't know if you
have a style across multiple Counter values and/or POs.
If it isn't enough to take care of it, then post back with
an explanation of what it does right/wrong.

 

[doyle60]

1) Yes, a style could be used on several different POs. There could
be 100s of T5656 and only one T565-6, for example.

2) I seem to think your code assumes that only one hyphen could appear
in a style number. Actually, some one could create the style 77-00-98
and another 770098. I want to find those as well.

Your present code returns no data and I am at loss on how to fix it.

Thanks so much,

Matt

 

[Marshall Barton]

1) Yes, a style could be used on several different POs. There could
be 100s of T5656 and only one T565-6, for example.

2) I seem to think your code assumes that only one hyphen could appear
in a style number. Actually, some one could create the style 77-00-98
and another 770098. I want to find those as well.

Your present code returns no data and I am at loss on how to fix it.

Drop the PO comparison from the ON clause.

OTOH, if the Counter field is not relevenat to this
operation and because of the multiple hyphens, then you do
need a replace kind of function. The one I used in A97 is
below and the query would be like:

SELECT T.Style, X.Style
FROM OrderDetails As T, OrderDetails As X
WHERE InStr(T.Style, "-") = 0
And InStr(X.Style, "-") > 0
And T.Style = Subst(X.Style, "-", "")


Function Subst(Original As Variant, Search As String, _
Replace As String) As Variant
Dim pos As Long

Subst = Original
If IsNull(Subst) Then Exit Function
If Len(Search) > 0 Then
pos = InStr(Subst, Search)
Do Until pos = 0
Subst = Left(Subst, pos - 1) & Replace _
& Mid$(Subst, pos + Len(Search))
pos = InStr(pos + Len(Replace), Subst, Search)
Loop
End If
End Function

 

[doyle60]

Thanks. The Subst function works wonderfully. I decided to attack
the queries in my own way, however. Thanks again,

Matt