• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 33
  • Last Modified:

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"
  • 2
1 Solution
Bill PrewCommented:
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 PrewCommented:
Same code will work in VBA where Field1 and Field2 are variables, like:

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

Open in new window

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, ...no points wanted as Bill has answered your Q as posted, ...
I was just passing by...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now