Is substring matching possible with ms access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i want to match two table fields but one of the table field only matches at
3rd column and the succeeding 6 characters. Is these possible with ms access.
Please help.

thanks.

alvin
 
i want to match two table fields but one of the table field only matches at
3rd column and the succeeding 6 characters. Is these possible with ms access.
Please help.

thanks.

alvin
Click to expand...

Yes, however a Query joining on these partial fields will be slow and
cannot be updated (because you cannot index a partial field). If
TableA contains the larger field (FieldA: AB123456XXX say) and TableB
contains FieldB: 123456, and you want to create a Query joining on the
123456, you can do so:

SELECT TableA.*, TableB.*
FROM TableA INNER JOIN TableB
ON Mid(TableA.[FieldA], 3, 6) = TableB.FieldB;

The need to do this indicates that you should consider a structural
redesign: the bigger field ("FieldA") is not "atomic". Consider
splitting it into two or three fields so that the six-character
portion is in a field of its own. If you need a composite field for
display, you can create it by concatenating the pieces in a Query.

John W. Vinson[MVP]
 
Back
Top