Prev: Create a Query that Prompts for Date and Time
Next: Help With SQL Update
From: cherman on 5 May 2010 17:38
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!
From: Dorian on 5 May 2010 17:58
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!
From: Jerry Whittle on 5 May 2010 18:07
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!
 | 
Pages: 1
Prev: Create a Query that Prompts for Date and Time
Next: Help With SQL Update