Retrieve value after "A" in text string 1

[Lost500]

Hi everybody!

the title says it all, i have a text field that contains a name then an Abstract number and i would like to get the Abstrat number (or the value in the string after "A-") so for example a record would show:

Lost500 Survey, A-74

I would like to get the A-74 out of there!! there is always an A followed by the - and a number. very rarely if at all does the survey name start with A, but even still it could be the value after "A-"

I really can't remember how to do this and any help would be great!

thanks in advance!
Lost

 

[dhookom]

You can combine the Instr() and Mid() functions to find and return the appropriate section of the string.

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

awe come on dhookom, you're my fav on here and i know that you know that i can't put that together... lol could you by any chance provide the combined code to extrat the "74" out of "Lost500 Survey, A-74"

the constant isn't a given position but the number values following "A-"

again thanks for all the help and you're all the best!

 

[dhookom]

Awe come on Lost500. Do you always want to be "Lost"? Don't you want a handle change to "Finder500" or "MVP500"?

If you are satisfied with being Lost, try an expression like:

   Mid([Unnamed Field], Instr([Unnamed Field],"A-")+2)

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

ouch dhookom, I was just being funny about being confused on how to combine the functions, which i tried before i asked. So we use the Instr to find a point in the string, mine being A-, and then with Mid and +2 it returns the next two values? i think i understand now. Neverless, this worked great and I appreciate the help.

 

[dhookom]

I hope you took my reply with the humor I intended to convey. I guess I should have put some of these ;-) ;-) ;-) ;-) ;-) in the message ;-)

The Instr() is like a "Where in one string do I find another". It returns the position of the first match it finds.

The Mid() is like "Take this string and return characters beginning at a position". Mid can use another argument after the position that sets the number of characters to return. If the last argument is not used, all characters to the end of the string are return ;-)

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

... Mid and +2 it returns the next two values?

Not quite.

Mid(..., Instr([Unnamed Field],"A-")+2)

says find the character position where "A-" starts (the Instr function), and then, starting two characters after that (+2), return all the characters to the end of the string.