By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,648 Members | 1,634 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,648 IT Pros & Developers. It's quick & easy.

Forumla to get part of the string

P: n/a
Hi,
Im trying to write a query which takes part of the string for example:

peter_Poland
michael_Germany

I wish to get output:
peter
michael

regards
Piotr

Nov 25 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Type somehing like this into the Field row in your query:
Left([Field1], Instr([Field1], "_") -1)
replacing Field1 with the name of your field.

To avoid the error for fields that have no underscore, enter this into the
Criteria row as well
Like "?*_*"

(That expression almost looks like a piece of ASCII-art.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Piotr" <ho***@wp.pl> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Hi,
Im trying to write a query which takes part of the string for example:

peter_Poland
michael_Germany

I wish to get output:
peter
michael

regards
Piotr

Nov 25 '05 #2

P: n/a
On 24 Nov 2005 23:55:50 -0800, "Piotr" <ho***@wp.pl> wrote:
Hi,
Im trying to write a query which takes part of the string for example:

peter_Poland
michael_Germany

I wish to get output:
peter
michael

regards
Piotr


If strName is the holder for the above names, then using:

mid(strName,1,instr(1,strName,"_")-1)

will give you the first name.

Note that this will only work as long as the underscore character
actually exists in the strName holder. If it doesn't, the function
will give you an error. A way around it, if that is a possibility, is
to do:

Iif(instr(1,strName,"_")=0,"",mid(strName,1,instr( 1,strName,"_")-1))

but this isn't needed if your data always has an underscore in it.

mike
Nov 25 '05 #3

P: n/a
Piotr wrote:
Hi,
Im trying to write a query which takes part of the string for example:

peter_Poland
michael_Germany

I wish to get output:
peter
michael

regards
Piotr


IOW you want the part of the column before the "_" ?

Left(fieldname,instr(1,fieldname,"_")-1)
Nov 25 '05 #4

P: n/a
Im keep on trying do this query and I still get statement error ?

Exam: Left([net_hal],instr(1,[net_hal],"_")-1)

What can be wrong ?

Im trying tp use InStr alone and doesnt work also ?
Could be no such function in my access ?

Nov 25 '05 #5

P: n/a
wow thank its working I had to put ; instead of ,

Nov 25 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.