Excel query not returning expected values in range 1

[cpjeffm]

I've got a spreadsheet that is querying inventory. I have a parameter setup so I can enter two values - a starting inventory location and an ending inventory location. In Microsoft Query, my criteria formula looks like this...

>=[Start Location] And <=[End Location]

The problem I'm having is if I enter a start location of OS1-1 and an end location of OS1-155, it's only showing me locations that start with OS1-1. Examples, OS1-1, OS1-10, OS1-11, OS1-12, etc. There are locations like OS1-3 that aren't showing. What am I doing wrong?

Thanks!

 

[SkipVought]

Hi,

That’s because your stock locations are alpha-numeric not numeric.

So the characters “111” sorts before the character “2”.

So you’re gonna need to parse the values on the “-“ delimiter using the Data > Texy to columns... feature to produce TWO columns of data: column 1 will be TEXT and column 2 will be NUMERIC (or General). Then collate on column 1 then 2.

Alternatively, you could do this in your query using the Left() and Right() string functions and Find() to locate the delimiter.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

So I did a test query...

SELECT `Sheet1$`.StkLoc
FROM `C:\Users\Skip\Documents\tt-queryStringCollation.xlsx`.`Sheet1$` `Sheet1$`
[b]WHERE (Left(StkLoc,3) & '-' & [highlight #FCE94F]format(right(StkLoc,len(StkLoc)-4),'000')[/highlight]<='OS1-155')[/b]

...and got this result...
[pre]
OS1-1
OS1-2
OS1-3
OS1-4
OS1-5
OS1-11
OS1-12
OS1-13
OS1-14
OS1-150
OS1-151
OS1-152
OS1-153
OS1-154
OS1-155
[/pre]

Notice that I use [highlight #FCE94F]Format[/highlight] to create a string of numeric digits with leading ZEROS that will facilitate a proper collation. You simply need enough places to accommodate your largest number following the delimiter.

For simplicity, I also ASSUMED that the delimiter is in the 4th character of the StkLoc string, which may not ALWAYS be the case.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[cpjeffm]

Thanks. Work around is to enter OS1-1 thru OS1-9. That picked them all up. Thanks!

 

[SkipVought]

Then you would also need OS1-10 thru OS1-99 and OS1-100 thru OS1-155.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}