Access rookie...need help separating a field...

[Mike375]

Attached. No laughing

The form is Split and its query is Split.

If you enter a name directly into the form to test then click another field so the query does the first split.

If you had to heaps of records and did not need the removal of various characters then you simplify the query greatly because Replace is slow.

 

[EraserveAP]

raskew I am not re-inventing anything; I simply made suggestion in Post#2 which pre-ceded Post #5 and I even said in Post #2 it would probably break if used past splitting on the comma in different wording of course. Given the later suggestions, such as Post #5, I certainly do not suggest that my suggestion in Post #2 be used past the comma if at all.

In Post 13, after reading the “examples given”, I posed a question on where I thought Allen Browne's code may break; I stated then that I had not tested it, this was quite clear. I was concerned, nothing more nothing less.
Bob Larson seemed to confirm this concern in his post because you really can not be sure about names. There is no golden rule to break them up that will work flawlessly for every name. It may parse them flawlessly but who is say exactly where the Last Name begins and the Middle Name ends when you plug them into new fields? That was the whole point of my question in Post #13 and I am sure that was what Bob Larson was trying to relate in Post #14. You can not just rely on where the spaces occur that will fail eventually. Some full names may have more than 3 spaces maybe even as high as 6 who knows for sure? Sure Allen Browne's code will work for the majority of the names in a given list and undoubtedly on some lists it will work for all the names. Rest assured though that you will run into rule breakers at some point, it is a certainty. You can definitely use Allen Browne's code as a base to improve upon, nothing wrong with that.
All I am saying/implying is you or whoever should QC the Table when this code is finished; not doing so could be bad and if you are doing something important or important and work related that would be very bad.

In Post #17 all I did was explain my reasoning for my suggestion in Post #2, and I quote from Post #17 “My thoughts in my original post with the split function were” if that ticked you off I apologize now, it certainly was not my intent. I was simply trying to relate my thought process on it.

By the way Mike375 that is a nice tool that have there.

 

[Mike375]

I have some later versions and also some for cleaning up phone numbers so the format is the same for duplicate checking but I am not presently where my external hard drives are at.

That one I posted I think has a problem with type mismatch on some entries but I can't remember what they were.

I often look back on things I made years ago and marvel at what "work arounds" can achieve The one I made to drop all spaces and - _ () etc from phone numbers took me forever to make but now I can do it with a simple function.