434,870 Members | 2,484 Online
Need help? Post your question and get tips & solutions from a community of 434,870 IT Pros & Developers. It's quick & easy.

a2k - how to split up field contents

 P: n/a Hi, It's been a while since I posted but good ol' google groups is confusing me these days and not giving me nice answers. I have this field in a table with some data HI1 HI2 HI1 ENG1 GEO1 ENG2 GEO2 GEO3 HI3 HI3 GEO3 ENG23 They will always be alphanumeric and end in a number. I'd like to be able to query this and end up with the following values; HI ENG GEO So what happens is that we lose the numbers and then find the unique remaining occurrences. Can a SELECT query handle this? Merry Xmas! Martin Nov 13 '05 #1
4 Replies

 P: n/a aircode select left(fld,len(fld)-1) from myTable group by left(fld,len(fld)-1) On 29 Dec 2004 12:14:57 GMT, "Pilocarpine" wrote: Hi,It's been a while since I posted but good ol' google groups isconfusing me these days and not giving me nice answers.I have this field in a table with some dataHI1HI2HI1ENG1GEO1ENG2GEO2GEO3HI3HI3GEO3ENG23They will always be alphanumeric and end in a number. I'd like to be able to query this and end up with the following values;HIENGGEOSo what happens is that we lose the numbers and then find the uniqueremaining occurrences.Can a SELECT query handle this? Merry Xmas!Martin ********************** ja**************@telusTELUS.net remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security Nov 13 '05 #2

 P: n/a Jack MacDonald wrote: aircode select left(fld,len(fld)-1) from myTable group by left(fld,len(fld)-1) Is the 1 there to take off the final numeric? Only problem is that I could have something like ENG123. Nov 13 '05 #3

 P: n/a Yes it is. I missed your final example -- I thought they were all single-digit suffixes. You will need a custom VBA function to locate the breakpoint. SELECT AlphaPart(fld) from myTable group by AlphaPart(fld) (aircode - no error checking) function AlphaPart(psFieldValue as string) as string dim i as integer for i = 1 to len(psfieldvalue) if isnumeric(mid(psfieldvalue,i,1)) then exit for next i alphaPart = left(psfieldvalue,i-1) end function On 29 Dec 2004 20:57:36 GMT, "Deano" wrote: Jack MacDonald wrote: aircode select left(fld,len(fld)-1) from myTable group by left(fld,len(fld)-1)Is the 1 there to take off the final numeric? Only problem is that Icould have something like ENG123. ********************** ja**************@telusTELUS.net remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security Nov 13 '05 #4

 P: n/a Jack MacDonald wrote: Yes it is. I missed your final example -- I thought they were all single-digit suffixes. You will need a custom VBA function to locate the breakpoint. SELECT AlphaPart(fld) from myTable group by AlphaPart(fld) (aircode - no error checking) function AlphaPart(psFieldValue as string) as string dim i as integer for i = 1 to len(psfieldvalue) if isnumeric(mid(psfieldvalue,i,1)) then exit for next i alphaPart = left(psfieldvalue,i-1) end function Cheers Jack, thanks for your time. I'll take a look at this once the coffee works it's magic. Nov 13 '05 #5