How copy everything from the first space

If I have this text in a field:

"1234 West Springfield Avenue"

How can I copy everything from the first space to another field?

In other words, the copied text would be "West Springfield Avenue"
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
If you mean in a query then a calculation like below will extract the part you wanted.  Or were you looking for VBA code?

Right(Field1,Len(Field1)-InStr(Field1," "))

Open in new window

SteveL13Author Commented:
VBA Code.  Sorry.  I should have been more clear.
Bill PrewIT / Software Engineering ConsultantCommented:
Same code will work in VBA where Field1 and Field2 are variables, like:

Field2 = Right(Field1, Len(Field1) - InStr(Field1, " "))

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
No Points wanted,
To get just the number you could use:
StreetNumber: Left([CustAddress],InStr([CustAddress]," "))

Not sure what you meant about "Copy"

But using these examples you could build a query like this to display the original field and the two "derived" fields, ...then you could "Use" the derived field anywhere you liked, without having to "copy" anything.
SELECT CustID,CustName, CustAddress, Left([CustAddress],InStr([CustAddress]," ")-1) AS CustStreetNumber, Right([CustAddress],Len([CustAddress])-InStr([CustAddress]," ")) AS CustStreetName
FROM tblCustomers;

derived fields
But again, points wanted as Bill has answered your Q as posted, ...
I was just passing by...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.