Comparing names

[Skip821]

I'm new to access and ive been running around trying to figure out this. I can bring in data with one query from two columns that will look like this below. I need to make sure that the MP01A in column 1 matches MP01A in column2 while ignoring the - in the comparison. If it matches the results don't need to be displayed, but I would like it to display if if column 2 doesnt match column 1. For instance column 2 could be XXX-MP-02-YYY or XXX-MP-01B-YYY.

Column1
XXX_MP01A_ZZZ
Column2
XXX-MP-01A-YYY

 

[JHB]

You can use InStr or an UDF. My solution is based on an UDF.
Paste the following into a Module

Public Function StringPart(SP As String, Sought As String)
  Dim x As Integer, y As Integer, z As Integer
  
  x = InStr(1, SP, Sought)
  y = InStr(x + 1, SP, Sought)
  If Sought = "-" Then
    z = InStr(y + 1, SP, Sought)
    StringPart = Mid(SP, x + 1, y - (x + 1)) & Mid(SP, y + 1, z - (y + 1))
  Else
    StringPart = Mid(SP, x + 1, y - (x + 1))
  End If
End Function

The query, (remember to change the table name and field name to what you call them).

SELECT Column1, Column2
FROM Columnx
WHERE (((StringPart([Column1],"_"))<>StringPart([Column2],"-")));