Microsoft Query

I'm querying external data from Excel using Microsoft Query. I'm querying an
oracle database. I'd like to limit the amount of records that come accross
so the file is easier to manage. We have 9 digit sales part numbers. Each
digit has significance. I'd like to isolate three characters, 4-6, and bring
only those records where these digits are equal to 123. I tried adding a
column in Microsoft Query, but was unable to make a mid function work.

Is there any hope? I'd prefer to use Excel query tool due to the
limitations of linked tables with Microsoft Access, and ultimately we need
the report in Excel so it is useful to the end user.

In MS Query, go to the Criteria menu and select Add criteria. You should
then see an area between the tables and the date where there is a space for
Criteria, Value and or. So if your part number field is named PartNo, try
putting the following criteria in as follows:

Criteria: LEFT(PartNo,3)
Value: 123

This should limit the output to only those part numbers that begin with 123.


"Excel GuRu" <ExcelGuRu@discussions.microsoft.com> wrote in message
news:FC9B73F1-75CC-44D3-B3E3-4D23F076BA85@microsoft.com...
> I'm querying external data from Excel using Microsoft Query. I'm querying
an
> oracle database. I'd like to limit the amount of records that come
accross
> so the file is easier to manage. We have 9 digit sales part numbers.
Each
> digit has significance. I'd like to isolate three characters, 4-6, and
bring
> only those records where these digits are equal to 123. I tried adding a
> column in Microsoft Query, but was unable to make a mid function work.
>
> Is there any hope? I'd prefer to use Excel query tool due to the
> limitations of linked tables with Microsoft Access, and ultimately we need
> the report in Excel so it is useful to the end user.

Mike, thanks for the quick response , but I couldn't get this to work the way
you described.

"Michael Malinsky" wrote:

> In MS Query, go to the Criteria menu and select Add criteria. You should
> then see an area between the tables and the date where there is a space for
> Criteria, Value and or. So if your part number field is named PartNo, try
> putting the following criteria in as follows:
>
> Criteria: LEFT(PartNo,3)
> Value: 123
>
> This should limit the output to only those part numbers that begin with 123.
>
>
> "Excel GuRu" <ExcelGuRu@discussions.microsoft.com> wrote in message
> news:FC9B73F1-75CC-44D3-B3E3-4D23F076BA85@microsoft.com...
> > I'm querying external data from Excel using Microsoft Query. I'm querying
> an
> > oracle database. I'd like to limit the amount of records that come
> accross
> > so the file is easier to manage. We have 9 digit sales part numbers.
> Each
> > digit has significance. I'd like to isolate three characters, 4-6, and
> bring
> > only those records where these digits are equal to 123. I tried adding a
> > column in Microsoft Query, but was unable to make a mid function work.
> >
> > Is there any hope? I'd prefer to use Excel query tool due to the
> > limitations of linked tables with Microsoft Access, and ultimately we need
> > the report in Excel so it is useful to the end user.
>
>
>

Excel GuRu wrote:
> > I'm querying
> > an
> > > oracle database. I'd like to limit the amount of records that
come
> > accross
> > > so the file is easier to manage. We have 9 digit sales part
numbers.
> > Each
> > > digit has significance. I'd like to isolate three characters,
4-6, and
> > bring
> > > only those records where these digits are equal to 123.
> >
> > try
> > putting the following criteria in as follows:
> >
> > Criteria: LEFT(PartNo,3)
> > Value: 123
> >
>
> thanks for the quick response , but I couldn't get this to work the
way
> you described.

Try Oracle syntax:

SUBSTR(PartNo,4,3)

Jamie.

--

Jamie,
Where do I type the formula and will I get a separate column that will
isolate the string I'm looking for, or will I merely be able to query on that
string? This is all new to me so I need some more specific instructions.
Thank you

"Jamie Collins" wrote:

>
> Excel GuRu wrote:
> > > I'm querying
> > > an
> > > > oracle database. I'd like to limit the amount of records that
> come
> > > accross
> > > > so the file is easier to manage. We have 9 digit sales part
> numbers.
> > > Each
> > > > digit has significance. I'd like to isolate three characters,
> 4-6, and
> > > bring
> > > > only those records where these digits are equal to 123.
> > >
> > > try
> > > putting the following criteria in as follows:
> > >
> > > Criteria: LEFT(PartNo,3)
> > > Value: 123
> > >
> >
> > thanks for the quick response , but I couldn't get this to work the
> way
> > you described.
>
> Try Oracle syntax:
>
> SUBSTR(PartNo,4,3)
>
> Jamie.
>
> --
>
>

Excel GuRu wrote:
> Where do I type the formula and will I get a separate column that
will
> isolate the string I'm looking for, or will I merely be able to query
on that
> string?

You can create a new column in addition to the original. Best to choose
an alias for the new column e.g.

SELECT
MyColumn,
SUBSTR(MyColumn,4,3) NewExpressionAlias
FROM MyTable;

Jamie.

--