If it works in most cases, then most likely the problem is with the data in those cases where it doesn't - a trailing space is a typical culprit.
The correct term for a field used in a relationship is matchfield. Matchfields can be calculation fields, as long as they are stored (it's a bit more complicated than that, but it should suffice for now).
OK, I'll check on those things. I did try to store the the calculated field but it wouldn't let me. -Not sure why.
I did try to store the the calculated field but it wouldn't let me. -Not sure why.
Now you got me worried: if the calculation references only the N_Number field in the same table, there's no reason why it cannot be stored. Is N_Number itself a calculation?
BTW, make sure that all fields involved are of type Text (or return a result of type Text) - since neither "1234B" nor "N1234B" are numbers.
The whole object I am trying to accomplish is to identify owners in the FAA Registry who are not members of our association (Malibu/Mirage Owners & Pilots Association),
Let me clarify by identifying the actual table names and field names: Table 1 = FAA Registry Table 2 = MemberInfo Table.
In the FAA Registry table there is a field called N_Number which is a text field, not calculated. Example: 1234B.
There is also a field I added to this table called N Number Edited, which is a calculated field. The calculation is "N"&N_Number. (I did this to get the N in front of the 1234B, so it will then be N1234B as an example.)
The field N Number Edited is the matching field to match the N Number field in the MemberInfo Table.
In the FAA Registry Table layout I added a calculated field called Member Name. The calculation is: Memberinfo Table::LFName. I made sure the Result is Text. N_Number is also Text.
Now, if I try to store Member Name, I get this error message: "The calculation "Member Name" cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage".
OK, that makes sense. The Member Name field is irrelevant here, because it is not a matchfield. IOW, this field uses the relationship, the relationship does not use it. Your matchfields are MemberInfo Table::N Number and FAA Registry::N Number Edited, and hopefully the latter is a stored calculation.
BTW, you don't really need the Member Name field in the FAA Registry Table: you could simply place the LFName field on the layout of FAA Registry Table.
Thanks to all your help, I seem to have it working now. There are still a few records that are not finding a match when there should be one, but I did find 2 of them that had a trailing space. Once I removed the space, they worked.
I don't want to go through 1137 records one at a time looking for trailing spaces. Can I trim these fields to automatically remove the trailing spaces? They are variable lengths.
If it's only a few records, you could simply find them. Anyway, have a look at the Trim() function.
Yeah, I already figured that one out, although I made a lot of extra work for myself...I forgot to check Result is: Text, so it converted all those records to 1.00. I had to reload the database, but all is well now.
Thanks again for all your help.