Parsing data in a Query

[rwagner]

I am trying to create a query and I am new at programming. A client sent the First Name in a field that is free form and could contain 2 first names that are septerated for either "&" "/" "OR" "AND" in between the names. I need to seperate the names into 2 fields.

For Example the field could read"

Bob
Bob/Susan
Bob/ Susan
Bob & Susan
Bob or Susan
Bob and Susan

Any suggestions or guidance on how to complete this query to get 2 field?

Thank you.

 

[Beetle]

First and foremost, if you are going to attempt any of the following, do so on a backup copy of your database, in case any of the results are not what you expected

Based on your example data it looks like &, or, and will have leading and trailing spaces but / may or may not have a leading and/or trailing space, so the first thing I would do is replace the / with a space using the Replace function;

Replace([YourField],"/", " ")

Then, to get the first of the two names I would check for the existence of a space. If there is one take everything to the left of it. If there is not one then there is only one name in the field so just take the field. This uses a combination of the IIf, InStr and Left functions;

IIf(InStr(1,[Field2]," ")>0,Left([Field2],InStr(1,[Field2]," ")),[Field2])

To get the second of the two names I would use InStrRev to find the last space before the end of the string. InStrRev works backward from the end of the string but the position it returns is from the beginning of the string, so you then take everything after the last space by using the Right function and subtracting the position returned by InStrRev from the total length of the string. In this case if there is no space then we don't want to take anything from the field, so we use a combination of the IIf, InStr, Right, Len and InStrRev functions;

IIf(InStr(1,[Field2]," ")>0,Right([Field2],Len([Field2])-InStrRev([Field2]," ")),"")

This can all be done via an Update query. I would probably leave the original field intact and push these updates into new fields in your table. Once you have the results you want you can delete the original field if you no longer need it. You can find more info about the above mentioned functions in the Access help files.