Hi Folks, I just heard of Airtable today, and I’m looking to see if it would be easier to use for me for an Access Family History project for personal use.
I want to create a Formula field that simplifies surnames beginning with “Mac” & “Mc” to create a “Sort Surname” field where all “Mac” surnames become “Mc” surnames. Then they will sort together, since the two versions are often mis-spelled even for the same person.
I have created a formula using my Surname field as follows (I used 300 for the mid command as I do not know the length of the surname field. Putting it this high should avoid errors)
It works perfectly for MacGillivray, Mackintosh, Macdonald etc, but it is not working for every non “Mac” surname. For example, it is turning Lumsden into Mcsden.
What am I doing wrong so that the Logical test is not working. I had it working perfectly in Access.