Find a number of characters right of a particular character.

[Jpacheco]

For example, the data is a field is formatted:

ARCHIVE #12345/98765

I need to run a query to pull only the 12345 from this field. Does anyone
know how to do this?

Thank you.

 

[Ofer]

What are the rules for the number?
Does it always start in the same place, location 6 in the string
Does it has a fixed length?
Does it always follow the # char?
Is there always the / char in the end of it?

 

[Jpacheco]

It does not always start at the same place in the field but it will always
follow the # char.
There should always be the / char at the end.
It is not always a fixed length. It will vary from 3 to 6 characters.

Thanks for the quick response.

 

[Albert D.Kallal]

It does not always start at the same place in the field but it will always
follow the # char.
There should always be the / char at the end.
It is not always a fixed length. It will vary from 3 to 6 characters.

Ok, now that we got the details. The follwing expression in a query builder
will return the number

Mynum:split(split([yourfieldname],"#")(1),"/")(0)

So, you can use the above in reprots etc.

 

[Jpacheco]

Thank you for your reply. I am receiving an error message when I enter
split(split([yourfieldname],"#")(1),"/")(0). The error is "The expression
you entered has an invalid .(dot) or ! operator or invalid parentheses." The
open partheses before the 1 is then highlighted. Any idea?

It does not always start at the same place in the field but it will always
follow the # char.
There should always be the / char at the end.
It is not always a fixed length. It will vary from 3 to 6 characters.

Ok, now that we got the details. The follwing expression in a query builder
will return the number

Mynum:split(split([yourfieldname],"#")(1),"/")(0)

So, you can use the above in reprots etc.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal

 

[Ofer]

Try this

mid(Fieldname,instr(Fieldname,"#")+1,instr(Fieldname,"/")-instr(Fieldname,"#")-1)
Or, as Albert advice you, it should work

split(split([yourfieldname],"#")(1),"/")(0)

Change the yourfieldname to the field Name that you are using

--
I hope that helped
Good luck

Thank you for your reply. I am receiving an error message when I enter
split(split([yourfieldname],"#")(1),"/")(0). The error is "The expression
you entered has an invalid .(dot) or ! operator or invalid parentheses." The
open partheses before the 1 is then highlighted. Any idea?

It does not always start at the same place in the field but it will always
follow the # char.
There should always be the / char at the end.
It is not always a fixed length. It will vary from 3 to 6 characters.

Ok, now that we got the details. The follwing expression in a query builder
will return the number

Mynum:split(split([yourfieldname],"#")(1),"/")(0)

So, you can use the above in reprots etc.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal

 

[ivan_haohao]

32ÖÜÏþåû