Character Count

[josie]

Any advice or suggestions on how to achieve the following would be
apprectiates
I have been asked to support a Access 2002 db. On looking at the tables
there is a product number alpha numeric product number usually in the format
of AA\12345678. To date the numeric portion varies between 2 to eight
characters. To simplify a product label we want to a a constant 8 character
numeric section.

Is there a function which will count from the Right hand side of the current
product number to the '\' character so that strip that number of characters
from right of the product number & place them in a new field?

Thanks in advance

josie

 

[Allen Browne]

Instr() will locate the backslash.
Mid() will return the remaining chracters.

So, try:
Mid([Product], Instr([Product], "\") + 1)

Left(), Right(), and Len() may also be useful.

If you need something more powerful, this custom ParseWord() function will
parse a field:
http://allenbrowne.com/func-10.html
For this case you would get the last word, where backslash is the delimiter
like this:
ParseWord([Product], -1, "\")

 

[josie]

Thanks Allen for your advice will try later today

josie

Instr() will locate the backslash.
Mid() will return the remaining chracters.

So, try:
Mid([Product], Instr([Product], "\") + 1)

Left(), Right(), and Len() may also be useful.

If you need something more powerful, this custom ParseWord() function will
parse a field:
http://allenbrowne.com/func-10.html
For this case you would get the last word, where backslash is the
delimiter like this:
ParseWord([Product], -1, "\")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Any advice or suggestions on how to achieve the following would be
apprectiates
I have been asked to support a Access 2002 db. On looking at the tables
there is a product number alpha numeric product number usually in the
format of AA\12345678. To date the numeric portion varies between 2 to
eight characters. To simplify a product label we want to a a constant 8
character numeric section.

Is there a function which will count from the Right hand side of the
current product number to the '\' character so that strip that number of
characters from right of the product number & place them in a new field?

 

[josie]

Hi Allen

Many thanks - used your ParseWord function in the end as this looks more
useful in the longer term.

josie

Thanks Allen for your advice will try later today

josie

Instr() will locate the backslash.
Mid() will return the remaining chracters.

So, try:
Mid([Product], Instr([Product], "\") + 1)

Left(), Right(), and Len() may also be useful.

If you need something more powerful, this custom ParseWord() function
will parse a field:
http://allenbrowne.com/func-10.html
For this case you would get the last word, where backslash is the
delimiter like this:
ParseWord([Product], -1, "\")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Any advice or suggestions on how to achieve the following would be
apprectiates
I have been asked to support a Access 2002 db. On looking at the tables
there is a product number alpha numeric product number usually in the
format of AA\12345678. To date the numeric portion varies between 2 to
eight characters. To simplify a product label we want to a a constant 8
character numeric section.

Is there a function which will count from the Right hand side of the
current product number to the '\' character so that strip that number of
characters from right of the product number & place them in a new field?