manipulate inported table

  • Thread starter Thread starter Dennis Villareal
  • Start date Start date
D

Dennis Villareal

ok i have a field that shows

YLD=58415/TEN=67182/ELG=29.76
or
YLD=62793/TEN=77688/ELG=32.3
or
YLD=135470/TEN=146793/ELG=10.9
or
YLD=135470/TEN=146793/ELG=10.97
or
YLD=135470/TEN=77688/ELG=32.3
or
YLD=135470/TEN=77688/ELG=32.33

these are the only displays that i can think of

is there a way of having them show up in seprate query's?

like
YLD=58415
TEN=67182
ELG=29.76

or even better
58415
67182
29.76

PRD_SPEC_FLDS_1 is the field name.

any help would be great!!

thanks.
 
If they ALWAYS have YLD, /TEN, & /ELG then it is easy.
SELECT Mid([YourField], Instr([YourField], "YLD=")+1, InStr([YourField],
"/TEN") AS Value
FROM YourTable
UNION ALL SELECT Mid([YourField], Instr([YourField], "/TEN=")+1,
InStr([YourField], "/ELG") AS Value
FROM YourTable
UNION ALL SELECT Mid([YourField], Instr([YourField], "/ELG=")+1,
InStr([YourField], "/TEN") AS Value
FROM YourTable;
 
Karl thanks for your help so far,

i put the values in as you stated with a few little changes and this is what
shows

YLD:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"YLD="),InStr([PRD_SPEC_FLDS_1],"/TEN"))
shows
YLD=63704/
on this one i put the extra ) at the end to complete and took out the +1
because is was showing up as
LD=63704/T
how do i get it to show up as
YLD=63704 or 63704

TEN:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/TEN=")+1,InStr([PRD_SPEC_FLDS_1],"/ELG"))
shows
TEN=69267/ELG=31.44
this one i just added ) at the end to complete
how do i get it to show
TEN=69267 or 69267

ELG:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/ELG=")+1,InStr([PRD_SPEC_FLDS_1],"/TEN"))
Shows
ELG=31.44
this one is great but can it be displayed as
31.44


KARL DEWEY said:
If they ALWAYS have YLD, /TEN, & /ELG then it is easy.
SELECT Mid([YourField], Instr([YourField], "YLD=")+1, InStr([YourField],
"/TEN") AS Value
FROM YourTable
UNION ALL SELECT Mid([YourField], Instr([YourField], "/TEN=")+1,
InStr([YourField], "/ELG") AS Value
FROM YourTable
UNION ALL SELECT Mid([YourField], Instr([YourField], "/ELG=")+1,
InStr([YourField], "/TEN") AS Value
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


Dennis Villareal said:
ok i have a field that shows

YLD=58415/TEN=67182/ELG=29.76
or
YLD=62793/TEN=77688/ELG=32.3
or
YLD=135470/TEN=146793/ELG=10.9
or
YLD=135470/TEN=146793/ELG=10.97
or
YLD=135470/TEN=77688/ELG=32.3
or
YLD=135470/TEN=77688/ELG=32.33

these are the only displays that i can think of

is there a way of having them show up in seprate query's?

like
YLD=58415
TEN=67182
ELG=29.76

or even better
58415
67182
29.76

PRD_SPEC_FLDS_1 is the field name.

any help would be great!!

thanks.
Click to expand...
Click to expand...
 
Did not test before --

YLD:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"YLD=")+4,InStr([PRD_SPEC_FLDS_1],"/TEN")-5)

TEN:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/TEN=")+5,InStr([PRD_SPEC_FLDS_1],"/ELG")-(InStr([PRD_SPEC_FLDS_1],"/TEN=")+5))

ELG: Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/ELG=")+5)
--
KARL DEWEY
Build a little - Test a little


Dennis Villareal said:
Karl thanks for your help so far,

i put the values in as you stated with a few little changes and this is what
shows

YLD:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"YLD="),InStr([PRD_SPEC_FLDS_1],"/TEN"))
shows
YLD=63704/
on this one i put the extra ) at the end to complete and took out the +1
because is was showing up as
LD=63704/T
how do i get it to show up as
YLD=63704 or 63704

TEN:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/TEN=")+1,InStr([PRD_SPEC_FLDS_1],"/ELG"))
shows
TEN=69267/ELG=31.44
this one i just added ) at the end to complete
how do i get it to show
TEN=69267 or 69267

ELG:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/ELG=")+1,InStr([PRD_SPEC_FLDS_1],"/TEN"))
Shows
ELG=31.44
this one is great but can it be displayed as
31.44


KARL DEWEY said:
If they ALWAYS have YLD, /TEN, & /ELG then it is easy.
SELECT Mid([YourField], Instr([YourField], "YLD=")+1, InStr([YourField],
"/TEN") AS Value
FROM YourTable
UNION ALL SELECT Mid([YourField], Instr([YourField], "/TEN=")+1,
InStr([YourField], "/ELG") AS Value
FROM YourTable
UNION ALL SELECT Mid([YourField], Instr([YourField], "/ELG=")+1,
InStr([YourField], "/TEN") AS Value
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


Dennis Villareal said:
ok i have a field that shows

YLD=58415/TEN=67182/ELG=29.76
or
YLD=62793/TEN=77688/ELG=32.3
or
YLD=135470/TEN=146793/ELG=10.9
or
YLD=135470/TEN=146793/ELG=10.97
or
YLD=135470/TEN=77688/ELG=32.3
or
YLD=135470/TEN=77688/ELG=32.33

these are the only displays that i can think of

is there a way of having them show up in seprate query's?

like
YLD=58415
TEN=67182
ELG=29.76

or even better
58415
67182
29.76

PRD_SPEC_FLDS_1 is the field name.

any help would be great!!

thanks.
Click to expand...
Click to expand...
Click to expand...
 
SWEET!!!! thanks alot you wouldn't belive how much easier this make life for
me!!

thanks again

KARL DEWEY said:
Did not test before --

YLD:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"YLD=")+4,InStr([PRD_SPEC_FLDS_1],"/TEN")-5)

TEN:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/TEN=")+5,InStr([PRD_SPEC_FLDS_1],"/ELG")-(InStr([PRD_SPEC_FLDS_1],"/TEN=")+5))

ELG: Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/ELG=")+5)
--
KARL DEWEY
Build a little - Test a little


Dennis Villareal said:
Karl thanks for your help so far,

i put the values in as you stated with a few little changes and this is what
shows

YLD:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"YLD="),InStr([PRD_SPEC_FLDS_1],"/TEN"))
shows
YLD=63704/
on this one i put the extra ) at the end to complete and took out the +1
because is was showing up as
LD=63704/T
how do i get it to show up as
YLD=63704 or 63704

TEN:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/TEN=")+1,InStr([PRD_SPEC_FLDS_1],"/ELG"))
shows
TEN=69267/ELG=31.44
this one i just added ) at the end to complete
how do i get it to show
TEN=69267 or 69267

ELG:
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/ELG=")+1,InStr([PRD_SPEC_FLDS_1],"/TEN"))
Shows
ELG=31.44
this one is great but can it be displayed as
31.44


KARL DEWEY said:
If they ALWAYS have YLD, /TEN, & /ELG then it is easy.
SELECT Mid([YourField], Instr([YourField], "YLD=")+1, InStr([YourField],
"/TEN") AS Value
FROM YourTable
UNION ALL SELECT Mid([YourField], Instr([YourField], "/TEN=")+1,
InStr([YourField], "/ELG") AS Value
FROM YourTable
UNION ALL SELECT Mid([YourField], Instr([YourField], "/ELG=")+1,
InStr([YourField], "/TEN") AS Value
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


:

ok i have a field that shows

YLD=58415/TEN=67182/ELG=29.76
or
YLD=62793/TEN=77688/ELG=32.3
or
YLD=135470/TEN=146793/ELG=10.9
or
YLD=135470/TEN=146793/ELG=10.97
or
YLD=135470/TEN=77688/ELG=32.3
or
YLD=135470/TEN=77688/ELG=32.33

these are the only displays that i can think of

is there a way of having them show up in seprate query's?

like
YLD=58415
TEN=67182
ELG=29.76

or even better
58415
67182
29.76

PRD_SPEC_FLDS_1 is the field name.

any help would be great!!

thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top