Help with InStr in a query

I have values like "2010 WW14" and "2010 WW7" in a query and I'm trying to
grab the number part to the right of "WW". It will always be a 1 or 2 digit
number and I always want the format to be a number.

I'm currently using Left([WorkWeek2],(InStr(1,[WorkWeek2],"ww")-1)) to grab
the 4-digit year to the left of "WW", but I can't figure out how to alter it
for what I need above. Any suggestions?

Thanks!

Try:
mid(WorkWeek2,instr(WorkWeek2,'ww')+2)
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"cherman" wrote:

I have values like "2010 WW14" and "2010 WW7" in a query and I'm trying to
grab the number part to the right of "WW". It will always be a 1 or 2 digit
number and I always want the format to be a number.

I'm currently using Left([WorkWeek2],(InStr(1,[WorkWeek2],"ww")-1)) to grab
the 4-digit year to the left of "WW", but I can't figure out how to alter it
for what I need above. Any suggestions?

Thanks!

You're working to hard to get the year assuming that the data always looks
like that..

CLng(Val([WorkWeek2]))

Again assuming that the numbers after the WW always start at the 8th
character:

CLng(Mid(WorkWeek2,8))

The CLng function ensures it returns a number and not text.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"cherman" wrote:

I have values like "2010 WW14" and "2010 WW7" in a query and I'm trying to
grab the number part to the right of "WW". It will always be a 1 or 2 digit
number and I always want the format to be a number.

I'm currently using Left([WorkWeek2],(InStr(1,[WorkWeek2],"ww")-1)) to grab
the 4-digit year to the left of "WW", but I can't figure out how to alter it
for what I need above. Any suggestions?

Thanks!