Microsoft Query

[tj007]

Hi

I have created a pivot report via a odbc connection to Oracle 8i. I am using Excell & Microsoft query as the query tool. I am querying for vehicle numbers VHCL_ID. The nunbering is as follows:

9901-9935 1999 series
0021-0035 2000 series
0101-0135 2001 series
0201-0235 2002 series
0301-0335 2003 series

Here is problem. When I query for 2002 series with
VHCL_ID >'201' And <'236', The 2000 series (21, 22,23) vehicles are return with the query. I understand that the computer is somehow looking at 201 as 20.1 therefore including 21, 22, & 23 which are 2000 series vheicles. I am stumped.

 

[SkipVought]

tj,

The problem is with your data. It seems that the data was imported as numeric and stored as text.

Let me explain. 0021, to YOU is a string of 4 characters: &quot;0021&quot;. The system looked at 0021 and said, &quot;that's a NUMBER -- 21!&quot; Then it was CONVERTED to text as &quot;21&quot;

So if you have
&quot;201&quot;
&quot;21&quot;

sorted in ascending TEXT sequence just like that!

and, guess what? When you state the condition
[tt]
VHCL_ID >'201' And <'236'
[/tt]
if the VHCL_ID is &quot;21&quot;, its in the 2002 series because &quot;21&quot; is between &quot;201&quot; and &quot;236&quot;

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[combo]

Can you remove string indicators in condition (and have VHCL_ID >201 And <236) and get reasonable results?

combo

 

[tj007]

How do I go about remove the string conditions?

 

[SkipVought]

In your table you have to either

1. reformat all values to 4 places with leading zeros maintaining that field as text like

&quot;0021&quot;
&quot;0201&quot;

or

2. if ALL the values are just numeric strings, change the definition of the field to INTEGER and convert the text to numeric.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tj007]

Skip, I thought combo was suggesting performing this from within Excell. I can't mod the table.

 

[SkipVought]

You can't do it within your query cuz of what I described regarding the data.

If you can't change the source, then import the entire table and convert the text to numbers in excel

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tj007]

Yes, that is a solution. But, toooooooooo many rows. There are millions. The pull down selection method in Excel is perfect. Thanks again

 

[SkipVought]

try this...

Clng(VHCL_ID) >201 And <236

Converts the value to LONG

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tj007]

skip

Excel return an error from this criteria. It does'n like it.

 

[SkipVought]

it probably rellay wants...

Clng(VHCL_ID) >201 And Clng(VHCL_ID) <236

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[rdroske]

If that last one didn't do it copy the actual SQL statement that MS Query is sending to Oracle and post it (View SQL in MS Query).

You might have to skip criteria etc (ie building the query though MSQuery) and just paste in a query that works.