grab partial data from one cell

[andiwir]

dear all,
im very newbie using ms.access,
actually i would to select a partial data from one cell of record,

i have a table1 like this

RNC aal5 vcltpid
RNJKT07 b005ca TransportNetwork=1,AtmPort=ES1-7-4-43,VplTp=vp1,VpcTp=1,VclTp=vc36
RNJKT07 b005da TransportNetwork=1,AtmPort=ES1-7-4-43,VplTp=vp1,VpcTp=1,VclTp=vc36
RNJKT07 b005qa TransportNetwork=1,AtmPort=ES1-7-4-43,VplTp=vp1,VpcTp=1,VclTp=vc36
RNJKT07 b005cb TransportNetwork=1,AtmPort=ES1-7-4-44,VplTp=vp1,VpcTp=1,VclTp=vc36
RNJKT07 b005db TransportNetwork=1,AtmPort=ES1-7-4-44,VplTp=vp1,VpcTp=1,VclTp=vc36
RNJKT07 b005qb TransportNetwork=1,AtmPort=ES1-7-4-44,VplTp=vp1,VpcTp=1,VclTp=vc36

and i need the result of my query like this

RNC aal5 atmport


RNJKT07 b005ca ES1-7-4-43
RNJKT07 b005cb ES1-7-4-44

how about the criteria to give return like above? to grab ES1-7-4-43 from TransportNetwork=1,AtmPort=ES1-7-4-43,VplTp=vp1,VpcTp=1,VclTp=vc36

please help me to solved that.

[rpeare]

If your field is always the same length you can use the left and right functions

in your case

Left(RNC, 15) & Right(AAL, 10)

I may have miscounted spaces but you can play with it if I have.

[andiwir]

it can't use, because length of data not same, like this

TransportNetwork=1,AtmPort=ES1-7-2-9,VplTp=vp1,VpcTp=1,VclTp=vc45
TransportNetwork=1,AtmPort=ES1-7-4-44,VplTp=vp1,VpcTp=1,VclTp=vc44
TransportNetwork=1,AtmPort=ES1-7-2-IMA47_4,VplTp=vp1,VpcTp=1,VclTp=vc38

any solution?

[rpeare]

TransportNetwork=1,AtmPort=ES1-7-2-9,VplTp=vp1,VpcTp=1,VclTp=vc45
TransportNetwork=1,AtmPort=ES1-7-4-44,VplTp=vp1,VpcTp=1,VclTp=vc44
TransportNetwork=1,AtmPort=ES1-7-2-IMA47_4,VplTp=vp1,VpcTp=1,VclTp=vc38

I'm assuming because you only posted the ATMPORT portion and lopped off the beginning of the line that the ATMPORT is the problem. You can still do what I suggested you just have to get a little fancier.

instr(fieldname, "=") will give you the position where the = sign STARTS (in this case it should return 8)

and the format for the RIGHT function is right(string, length)

to get the lenght of the string you want to extract you would have to use a combination of the LEN of the string and the INSTR of the = sign

so you'd end up with

Right([fieldname],Len([fieldname])-InStr([fieldname],"="))

if the left portion of your first field also is variable you'd use the same sort of technique.

[andiwir]

thanks alot rpeare

based on your post
i modified your advice to use instr(), because im very confuse when use instr(),
so im using split() in array to grab the atmport,
first use recordset to create variable from query result, then fill array1 with split() with demiliter "," , then fill array2 again with split() again with demiliter "=" from array1(1)

array2(1) success to grab the atmport.