Remove first Word or Characters Before " " in Query (1 Viewer)

Peter1

New member
Local time
Today, 01:06
Joined
Nov 3, 2020
Messages
2
Hello, I was wondering if anyone can please help me, I have created a query in an access database and one of the fields called "Address L1" contains the first line of an address, I want to remove the first word or numbers before the first space (from the left) and display the remaining part in a new field;
For example, the existing text with the field is;
125 Green Road

I want to have the following in a new field;
Green Road

I have managed to extract the first word or characters before the first space that I need for another field and was trying to use the following for the above but returned the last two characters instead!
Expr1:Right([Address L1],(InStr(1,[Address L1],""))-1)

I would really appreciate any help.

Many thanks
Peter
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:06
Joined
Jan 20, 2009
Messages
12,849
Use Mid() instead of Right. The third argument can be an arbitrarily large number so long as it is longer than the rest of the string.

It is also a good idea to concatenate a space onto the end of the first argument so it won't throw an error if there is no space in the string.
 

isladogs

MVP / VIP
Local time
Today, 01:06
Joined
Jan 14, 2017
Messages
18,186
Code:
Mid([Address L1],InStr(1,[Address L1],"")+1)

Recommend not using spaces in field names
 

Peter1

New member
Local time
Today, 01:06
Joined
Nov 3, 2020
Messages
2
Hello,
Many thanks for the quick response and guidance, I have used the "Mid" as suggested, and works great, I used the following;
Address Road: Mid([Address L1],(InStr(1,[Address L1]," "))+1)

I take on board what you also mention about not using spaces in the field names and will look to change the way I create them.

thank you again for your help,

Best regards

Peter
 

onur_can

Active member
Local time
Yesterday, 18:06
Joined
Oct 4, 2015
Messages
180
Of course, care should be taken not to use spaces when using domain names. If your field names contain spaces, if you are going to use it in SQL or VBA code, do not forget to enclose it in square brackets. Only in this way can you reach the right result.
 

Users who are viewing this thread

Top Bottom