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" wrote in message
...
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" wrote in message
...
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.

--