Parse Trigger in Access 2003?

  • Thread starter Thread starter blameshifter
  • Start date Start date
B

blameshifter

Hello, friends...
I know you're likely tired of hearing this sort of question so I
apologize in advance for asking, but I am clueless as to how to go
about getting this accomplished.

I have a simple database field that includes two names separated by an
"&" - for example "John Smith & Mary Smith". I need to split these
fields and use the "&" as the trigger on where to split them and the
"&" needs to NOT be displayed in the output. This is probably a
simple task, but the problem is that I know zero about using Microsoft
Access 2003, so I don't even know where to start on doing this. If
you can offer any insight, that would rock.
 
Hello, friends...
I know you're likely tired of hearing this sort of question so I
apologize in advance for asking, but I am clueless as to how to go
about getting this accomplished.

I have a simple database field that includes two names separated by an
"&" - for example "John Smith & Mary Smith". I need to split these
fields and use the "&" as the trigger on where to split them and the
"&" needs to NOT be displayed in the output. This is probably a
simple task, but the problem is that I know zero about using Microsoft
Access 2003, so I don't even know where to start on doing this. If
you can offer any insight, that would rock.
Click to expand...

It's not as obvious as perhaps it should be.

Name1: Left([fieldname], InStr([fieldname], "&") - 1)
Name2: Mid([fieldname], InStr([fieldname], "&") + 1)


John W. Vinson [MVP]
 
I have a simple database field that includes two names separated by an
"&" - for example "John Smith & Mary Smith". I need to split these
fields and use the "&" as the trigger on where to split them and the
"&" needs to NOT be displayed in the output. This is probably a
simple task, but the problem is that I know zero about using Microsoft
Access 2003, so I don't even know where to start on doing this. If
you can offer any insight, that would rock.
Click to expand...


Another reason to not use one field for two items of data.

This may help for now:

SELECT fld,
Left(fld, InStr(fld, "&")-1) As Nm1,
Mid(fld, InStr(fld, "&")+1) As Nm2
FROM table
WHERE InStr(fld, "&") > 0
 
Back
Top