Modify tblCities to use a looked up value field option to copy the state text from tblStates into a matching field in tblCities.
Now your CityState calculation can use this field defined in tblCities for the state text to produce a result that can be stored and indexed.
I just created a State field in tblCities, then in Field Options had it lookup to tblStates::State.
Was expecting all the records to fill in, but nothing happened.
Do I need to export tblCities to Excel, do some vlookups to State, then re-import it?
Any record where you edit the ID field on which the lookup's relationship is based will automatically copy the data for you. It's only with existing records where you'll have this issue so you can do a one time fix to correct it.
You can use Relookup in the records menu to update all your existing records:
Put your cursor in the tblCities::StateID field on a layout based on tblCities after doing a show all records on this layout.
Choose Relookup from the records menu and it'll update them.
Wow! Ok ..!
Thanks Phil ..!!
Had already exported to Excel and done vlookups, but deleted all that, rebuilt everything, and did the Relookup Field Contents.