parse Number

[mr moe]

I have a field in a table that looks like this for example MNJUIIII#33, mnnn#2,
i want to parse the number in the query in a new column like 33, 2 how do I do that? Thanks.

 

[MSAccessRookie]

I have a field in a table that looks like this for example MNJUIIII#33, mnnn#2,
i want to parse the number in the query in a new column like 33, 2 how do I do that? Thanks.

Check out the following functions and come back if you still have questions:

• Instr() to locate the value to extract
• Mid() to extract the value from the Field

 

[mr moe]

Ok i'm back, i want all characters after the # sign I tried the expressions no luck please help.

 

[MSAccessRookie]

Ok i'm back, i want all characters after the # sign I tried the expressions no luck please help.

Please post what you tried and I will see if I can point you in the right direction.

 

[mr moe]

i tried RTrim(Mid([model],1,InStr([model]," ")-1)) it's not working
it's only parsing the first section. all i want is to extract field after the #sign for example mmmddd#44 i want to get 44,but I can't count cuz each field has different lenght. thanks for trying to help.

 

[ajetrumpet]

try this mr. moe:

right([model], instr([model], "#") - 1)

 

[MSAccessRookie]

i tried RTrim(Mid([model],1,InStr([model]," ")-1)) it's not working
it's only parsing the first section. all i want is to extract field after the #sign for example mmmddd#44 i want to get 44,but I can't count cuz each field has different lenght. thanks for trying to help.

The syntax for the Instr() Command should be:

Instr([StartPosition], SourceData, SearchString)

(Defaults to starting at Position #1)
​

The syntax for the Mid() Command is:

Mid(SourceData, SPos, [NumChars])

(Defaults to the number of characters remaining in the string)
​

 

[Brianwarnock]

Originally you said
I have a field in a table that looks like this for example MNJUIIII#33, mnnn#2,

I assumed that that was all 1 field and that you wished to extract the 2 numbers, if however they were 2 examples of the field then lookup InstrRev Right and Len althougth there is nothing wrong with instr and mid, it will just complete your education.


Brian

 

[Brianwarnock]

try this mr. moe:

right([model], instr([model], "#") - 1)

Just noticed this I think Adam meant to use InstrRev not instr.

Brian

 

[ajetrumpet]

no i didn't Brian. And...isn't that function only avialable in certain versions of Accesss anyway ?

 

[MSAccessRookie]

Originally you said
I have a field in a table that looks like this for example MNJUIIII#33, mnnn#2,

I assumed that that was all 1 field and that you wished to extract the 2 numbers, if however they were 2 examples of the field then lookup InstrRev Right and Len althougth there is nothing wrong with instr and mid, it will just complete your education.


Brian

OK, so I missed the boat. I thought the whole line was one entry, not two. In that case, the following might get what you want:

Right([model], Len(model)-(Instr([model], "#") + 1))

​

The syntax for the Right() Command should be:

Right(SourceData, NumberofCharacters)

​

 

[Brianwarnock]

I don't know which version it came in but Ok if the poster is on an early version he wont be able to use it, but hey does that mean we cannot give advice on functions that weren't available on the first release?

as to your code using instr on his example mmmddd#44 it would return 7 and he would extract the 6 right most digits, not what he wants, InstrRev would return 3 and he would extract the 44.

Brian

 

[Brianwarnock]

OK, so I missed the boat. I thought the whole line was one entry, not two. In that case, the following might get what you want:

Well I thought like that to start with but a later example changed my mind.
He now has several examples so his education is pretty complete and he can take his choice.

Brian

 

[mr moe]

Thanks guys I got it.