Match two similar string in a query

[lmarconi]

Good morning everyone. I have a query which I use to return the differences between the fields "Next Package to Start" and "TK_FRQ" (last 2).
Where is the problem: in some cases, unfortunately, the value are "the same" but written in different way (e.g. : M2 is 2M, Y1 is 1Y) which are similar but not equal so my query return as "different".


Any suggestion how to solve this matter, i should avoid to show records as these because are the same (below the query result example) :



Thank you for usual and kindly support.
LM

[Welshgasman]

If it is just the two characters then you could swap them and compare?
However now you will probably say that is not always the case?

You should not allow bad data to enter the system in the first place?
You could use combos to enter that data.?

So compare
NPTS to TKF
Right(NPTS,1) Left(NPTS,1) to TKF
NPTS to Right(TKF,1) Left(TKF,1)

etc

[Micron]

Agree - avoid bad data input by controlling what can be entered. I might compare the sum of the asci values (M+2 would be the same as 2+M) but not sure how at this point since none of those records appear to be exact dupes anyway. F'rinstance all records for 6M have different group counter values at least, so which records would you eliminate just because they all have 6M and M6 values?

[orange]

I agree with the others. If users are allowed to enter M2 and you are expecting/standardizing on 2M, then you have a data entry validation issue. Allow users to select from standardized values. The sooner the better.

[lmarconi]

First of all, thanks a lot for the answers.
I think that everyone agree the input should be controlled but, unfortunately, we are talking about Company's procedure, specification and legacy system (which i use to compare with) which cannot be changed, so I'm not in the condition to "force" the user to do what I would like, to avoid such problem. So my query should compare that what prepared by the user follow the rules in the legacy system. Just to explain the "NPTS" prepared by the user in the tool I provided, cannot be different from the TK_FREQ (coming from the legacy system and created in the past).
Thanks again.

[orange]

And how do you plan to do that given

Company's procedure, specification and legacy system (which i use to compare with) which cannot be changed, so I'm not in the condition to "force" the user to do what I would like, to avoid such problem.

the value are "the same" but written in different way

No, they are not the same. It is the validation procedure that is lacking.

Also, since

"NPTS" prepared by the user in the tool I provided, cannot be different from the TK_FREQ (coming from the legacy system and created in the past).

seems to be mandatory, there must be a change to your code or the user entry validation to ensure/enforce a correct/valid entry.

How critical is this to the company's business?

[lmarconi]

And how do you plan to do that given
For the moment, considering i noticed that should be only 2 characters (this is what coming from the unit TKS of the Legacy) , I will verify that the NTPS it is also different from the "
RIGHT, 1 & LEFT,1 of TKF" as suggested by Welshgasman even if in the future i may be in the condition to revise it but is the only solution i have now.

No, they are not the same. It is the validation procedure that is lacking.
The problem is that in the legacy system there is no validation procedure, as I said user can use one or another way without problem. So, looking at Y1 and 1Y as example, agree are not the same, but the meaning is the same so should not appear as error in the query.

Also, since seems to be mandatory, there must be a change to your code or the user entry validation to ensure/enforce a correct/valid entry.
As I said, i cannot force the user (which is a company's contractor) to use one or the other way because in the procedure (provided by the company which i'm not in the condition to change) there is no any indication so, in few words, they can do what they want (and i have the problem).

How critical is this to the company's business?

It is critical because is part of Data Quality verification and if the two values are not the same, data will be rejected.

[Welshgasman]

Thank you. That has made my day.

A company that allows any form of data to be entered and then wishes to quality check it.

There has to be a joke in there somewhere.

Oh yes, Closing the barn door, after the horse has bolted.

[orange]

@lmarconi,

I you can't change the legacy, or the company won't allow the contractor to change the legacy, then you have to modify your "compare code".
Here are a couple of routines that might help with logic. I did not check for 1 alpha and 1 numeric, but could be added if needed.

' ----------------------------------------------------------------
' Procedure Name: ValidateIt
' Purpose: Routine to do basic checks on an expected 2 character input value
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 25-Jun-21
' ----------------------------------------------------------------
Sub ValidateIt()
    Dim i As Integer
    Dim x(4) As String
    x(0) = "m2 "
    x(1) = "2m"
    x(2) = "m2"
    x(3) = "m24"
    x(4) = ""

    For i = 0 To UBound(x)
        If Len(x(i)) > 2 Then   'original input too long
            Debug.Print "original value <" & x(i) & ">  too long"
        ElseIf Len(Trim(x(i))) = 2 Then  'had leading/trailing space(s)
            Debug.Print "original value <" & x(i) & "> had leading otr trailing space(s)"
        ElseIf Len(x(i)) = 0 Then 'no value supplied
            Debug.Print "original value <" & x(i) & ">  was Null/empty"
        End If
     Next i
End Sub

' ----------------------------------------------------------------
' Procedure Name: getReverse
' Purpose: To interchange the 2 characters of input
' Procedure Kind: Function
' Procedure Access: Public
' Parameter x (String): 2 char string to be reversed
' Return Type: String
' Author: Jack
' Date: 25-Jun-21
' ----------------------------------------------------------------
Function getReverse(x As String) As String
    If Len(x) = 2 Then
        getReverse = Mid(x, 2, 1) & Mid(x, 1, 1)
    Else
        getReverse = "Error"
    End If
End Function

You can compare the value supplied or the reverse of the value supplied.

[Micron]

Maybe just a calculated query field AsciiTK_FREQ: Ascii([TK_FREQ]) with criteria as <> Ascii([NextPackageToStart])

If both of these 'systems' only have upper case characters, should work I think, but untested by me.

[lmarconi]

Thank you all to gives valuable suggestion and also spent time to prepare something to be used.
I use the getReverse function kindly prepared by orange. I just did a minor modification adding also the case of 3 characters (in the future I may have Y10 (10Y) or Y11 (11Y), etc.) as below:
'---------------------------------------------------
Function getReverse(x As String) As String
If Len(x) = 2 Then
getReverse = Mid(x, 2, 1) & Mid(x, 1, 1)
ElseIf Len(x) = 3 Then
getReverse = Mid(x, 3, 1) & Mid(x, 1, 2)
Else
getReverse = "Error"
End If
End Function
'------------------------------------------------------
This will solve my problem definitively.

I would like also to inform that i test the suggestion of the ASC (Micron) which also work good but, considering the lack of discipline of the users, if somebody will use not UPPER CASE letter, i may have again the problem. Also, for information, in this way i need to do one more step because I don't know why, if i use it directly in my query, it gives me "Type Error" when i compare my string with the ASC , so i need to create a temporary table to use for the test.

Thank you again.

[ArviLaanemets]

In case you can't change the way data is read/entered initially:

1. Create an 2-column table, like tblConversion: Str1, Str2, where field Str1 contains all possible variations of values, and Str2 contains matching standard values. Like
Str1 Str2
M2 M2
2M M2
Y9 Y9
9Y Y9
...

2. Now run an update query to replace all strings in table field(s) with standard one from tblConversion, to standardize all current entries.

3. For all future data, update any procedures/queries used for automatic data entry to validate those fields based on tblConversion. For manual data entry, add BeforeUpdate event(s) where entered data is validated.

[CJ_London]

I use a similar method of aliases which manages via a many to many relationship

Tblaliases
ParentPk…alias
1…………..1y
1……………y1
Etc
Both your tables join on the alias field and return the right record

I use this for abbreviations and terminology variations such as

Caps
Caplets
Cap

[moke123]

I would like also to inform that i test the suggestion of the ASC (Micron) which also work good but, considering the lack of discipline of the users, if somebody will use not UPPER CASE letter, i may have again the problem. Also, for information, in this way i need to do one more step because I don't know why, if i use it directly in my query, it gives me "Type Error" when i compare my string with the ASC , so i need to create a temporary table to use for the test.

I tested Microns method and it works for me in a query. I'm assuming that your data is not case sensitive.

I force the string to upper case and add the asc() values of each letter. Compare the 2 totals to see if they match.

Use a function to get the totals.

Function ReturnASCTotal(T1 As String) As Long
Dim y As Integer
Dim i As Integer


For i = 1 To Len(T1)
y = y + Asc(Mid((UCase(T1)), (i), 1))
Next i


ReturnASCTotal = y


End Function

[Micron]

I did notice that I incorrectly used Ascii when I meant Asc for the function name. I also thought about suggesting forcing to upper case but I gave up because of the objections of the need to add a table and something about an extra step. Good on ya, moke123!