Microsoft Query

[Guest]

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.

 

[Michael Malinsky]

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.

 

[Guest]

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

 

[Jamie Collins]

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.

--

 

[Guest]

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]

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.

--