If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Help with InStr in a query
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! |
#3
|
|||
|
|||
Help with InStr in a query
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! |
Thread Tools | |
Display Modes | |
|
|