MS Access: Looking to find a way to check for matches across two fields based on character position.

[canvasshoes2]

Apologies for the long involved title.  I'm super rusty and don't remember all the exact correct jargon.  I have a database with 30 or so fields.  One of those fields is just a simple Fiscal Year field.  2022, 2023, etc.

The second field is an alpha-numeric ID number.  Something like AAAA322219999.  The first four characters relate to a specific company location.  Such as AAAA would be Seattle, BBBB would be Chicago, etc.

The first set of numbers after the letters refers to the project type, and the second set of numbers after the letters is the last two digits of that project's fiscal year.  So, if a project is to be awarded for a Seattle project, prj type 1, in 2022, the first digits should be AAAA3222.

I'd like to have a way to check that the last two characters in the FY field correctly match the characters in places 8 and 9 in the ID field.  (Ideally, I'd like to do the same for the other parts of the "code" in the ID number as well, but for now, I'd be happy if someone would point me in the right direction).

[#2]

There is function RIGHT() to separate last two digit from string. Post few sample data and desired output. Attach a sample database (.accdb) file.

[#3]

@canvasshoes2 

There are two ways to approach the problem. One is hard, one a lot easier.

The hard way is to use this approach, in which three different data points are mushed into a single field, forcing you to figure out a trick to extract any one of them in order to use it. Hence the question here.

The easy way, which also turns out to be the way relational database design requires, is to create one field for each data point and put the data into their own fields. It's far, far easier to concatenate if you want to DISPLAY the mushed together value to a human user. It's also far easier for Access to manage the discrete data points if they remain discrete in the three fields in the table.

Follow the principles of proper table design, which is called Normalization, and your task is greatly simplified here and everywhere else you need to filter by location, or by project type, or by fiscal year, or any combination of the three!

[#4]

iif(right([the FY field name],2) = mid([the ID field name],8,2),"MATCHED","NOT MATCHED")

If opportunity presents, either change the data model so that each concept occupies one field (ie CompanyLocation, ProjectType, ProjectYear and so on), or failing that, use queries that split the complex fields into their 'atomic' bits (CompanyLocation etc) and always use those queries when accessing the data. This might be informative: https://www.techtarget.com/whatis/definition/data-abstraction