Field Copy

[Guest]

Hi, I hope you are doing well.

I need to know if there is a function or whatever I can use to copy field
data from the right or left of the field up to the first blank or comma or
whatever.

Any help will be greatly appreciated.

KP

 

[Steve Schapel]

Ken,

You might have to be a bit more detailed and a bit more precise, to get
a definitive answer. But maybe this example will help...
If YourField contains this data:
banana, apple
... then this expression:
Left([YourField],InStr([YourField],",")-1)
... will return:
banana

You can look in Access Help for more information on string functions
such as Left(), Right(), InStr(), InStrRev(), Len()

 

[Joseph Meehan]

Hi, I hope you are doing well.

I need to know if there is a function or whatever I can use to copy
field data from the right or left of the field up to the first blank
or comma or whatever.

Any help will be greatly appreciated.

KP

I might suggest that while Steve answered your question. I would like
to question your question.

Normally you would not do as you suggest. You would not save the
results in a new field. You would re-create the data each time you needed
it using a formula or a query. Doing as you appear to be suggesting will
tend to bog down Access and it often causes problems later when someone
finds out that they need to change part of that data and don't remember to
change the other field. Soon your data is a worthless unreliable mess.

 

[Guest]

Hi,
The process I'm working on is a one-time event. The data was entered in the
field as FName, MI, LName. They want to be able to sort by LName. Once I
capture the last name and put in a field of its own, the users will enter the
data in the new format with separate fields for the three data elements.

Thanks for your help.

 

[Guest]

Thanks Steve,
I checked the string functions but could not make heads nor tails out of how
to make it work in this particular situation. I appreciate your time and
assistance. I'm much smarter on this now.

Ken,

You might have to be a bit more detailed and a bit more precise, to get
a definitive answer. But maybe this example will help...
If YourField contains this data:
banana, apple
... then this expression:
Left([YourField],InStr([YourField],",")-1)
... will return:
banana

You can look in Access Help for more information on string functions
such as Left(), Right(), InStr(), InStrRev(), Len()

--
Steve Schapel, Microsoft Access MVP

Hi, I hope you are doing well.

I need to know if there is a function or whatever I can use to copy field
data from the right or left of the field up to the first blank or comma or
whatever.

Any help will be greatly appreciated.

KP

 

[Steve Schapel]

Ken,

As I mentioned before, I would need more details before I could offer
more specific help. I don't know "how to make it work in this
particular situation" either, because I have no idea what this
particular sitution is.

 

[Steve Schapel]

Ken,

Ah, ok, I see you mentioned something about the "particular situation"
in your response to Joseph. Well, probably you will obtain the LName
portion of the data by this...
Mid([YourField],InStrRev([YourField],",")+2)

You could use an Update Query to update the LName field in the table to
the above expression.