Joining fields - one table's string value is contained in the other table's value

[eschmidt12]

Hi guys,



First post here. I'm trying to join two fields in which the string values from one table are contained in the string values of the other. For example, one field might have a value of DEA, where the other table would contain 2FADEAMR, and another might have ADEATRF1. To filter for this, using *DEA* as a criteria works just fine for retrieving the records I'm looking for.

Is there a way to do this through a join, or do I have to create a subquery to extract the strings that I'm looking for? If I need to create a subquery to generate a field of "trimmed" values for the 2nd table, how would I do this?

Let me know if this doesn't make sense.

Thanks!!

E

[June7]

If the embedded string were always in the same place, could use Left or Mid function to extract. However, I don't see any consistency to the string structure and consistency is essential when parsing data.

Why is this value not broken into separate fields to begin with?

I tried a LIKE operator with wildcards and didn't work.

[eschmidt12]

If the embedded string were always in the same place, could use Left or Mid function to extract. However, I don't see any consistency to the string structure and consistency is essential when parsing data.

Why is this value not broken into separate fields to begin with?

I tried a LIKE operator with wildcards and didn't work.

Not sure why the field isn't separated, but unfortunately it is a field generated by the system that likely will never change.

My criteria is LIKE "*" & [Enter value] & "*", this seems to work.

[June7]

Generated by what system?

That will work to filter records but not to join tables. The join was what I tried.