Forumla to get part of the string

[Piotr]

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

[Allen Browne]

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

[Mike Preston]

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

[Trevor Best]

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)

[Piotr]

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 ?

[Piotr]

wow thank its working I had to put ; instead of ,