Problem: Sort Text As Number

[steph]

Hi,

I have a form that displays data from an external oracle-table. I have
2 coordinate fields in this table, namely X and Y, hey hold values like
X='A', Y='5', similar to the fields on a chess board.

I want to order my query results according to the coordinates like
'ORDER BY X,Y'. The problem is Y is a character field instead of a
number field so the resulting order looks like:
A1, A10, A11 etc.
instead of
A1, A2, A3, etc.

I tried to set the 'order by' property to someting like 'X,val(Y)' but
the only result is that I get a random result order. Can anybody help
me, I'm rather desperate on this.

The version is Access 2002, SP3

Thanks for any input,
Stephan

 

[Ken Snell [MVP]]

This should work for you:

ORDER BY [X], Val([Y])

Tell us more about the SQL statement in its entirety. Any chance that Y
would be empty or a nonumeric value?

 

[Douglas J. Steele]

Sorry, Ken, but that won't work.

Val stops reading the string at the first character it can't recognize as
part of a number, and since the values start with letters, that means it
won't return anything.

If it's always a single letter, you could use ORDER BY [X], Val(Mid([Y],2))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This should work for you:

ORDER BY [X], Val([Y])

Tell us more about the SQL statement in its entirety. Any chance that Y
would be empty or a nonumeric value?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I have a form that displays data from an external oracle-table. I have
2 coordinate fields in this table, namely X and Y, hey hold values like
X='A', Y='5', similar to the fields on a chess board.

I want to order my query results according to the coordinates like
'ORDER BY X,Y'. The problem is Y is a character field instead of a
number field so the resulting order looks like:
A1, A10, A11 etc.
instead of
A1, A2, A3, etc.

I tried to set the 'order by' property to someting like 'X,val(Y)' but
the only result is that I get a random result order. Can anybody help
me, I'm rather desperate on this.

The version is Access 2002, SP3

Thanks for any input,
Stephan

 

[John Vinson]

I tried to set the 'order by' property to someting like 'X,val(Y)' but
the only result is that I get a random result order. Can anybody help
me, I'm rather desperate on this.

Try basing the Form on a Query; include a calculated field

SortY: Val([Y])

Be sure this is to the right of X in the query grid. Specify Ascending
on X and on SortY.

I've found this to be simpler in general than messing with the Form's
OrderBy property; if you do want to use OrderBy, do include the SortY
field in your query anyhow, and set the form's OrderBy to

X; SortY

and also be sure that OrderByOn is True.

John W. Vinson[MVP]

 

[Klatuu]

I really don't know if this will work in this situation, but it is worth a try:
ORDER BY X & Format(Y,"00")
If X = "A" and Y = "1" It will come out as "A01"
If X = "A" and Y = "10" it will come out as "A10"
This will give you the correct sort order, but I have never used it in an
Order By clause, so it will be an experiment.

One other thing, if the lentth of Y can be greater than 2. you need to
include enought zeros in the format for the longest Y can be.

 

[Ken Snell [MVP]]

I agree with what you say, but I was understanding from the poster that X
and Y were separate strings, not "parts" of a single string. If his setup is
the latter, then yes (no? <g>), my answer isn't right.

--

Ken Snell
<MS ACCESS MVP>

Sorry, Ken, but that won't work.

Val stops reading the string at the first character it can't recognize as
part of a number, and since the values start with letters, that means it
won't return anything.

If it's always a single letter, you could use ORDER BY [X],
Val(Mid([Y],2))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This should work for you:

ORDER BY [X], Val([Y])

Tell us more about the SQL statement in its entirety. Any chance that Y
would be empty or a nonumeric value?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I have a form that displays data from an external oracle-table. I have
2 coordinate fields in this table, namely X and Y, hey hold values like
X='A', Y='5', similar to the fields on a chess board.

I want to order my query results according to the coordinates like
'ORDER BY X,Y'. The problem is Y is a character field instead of a
number field so the resulting order looks like:
A1, A10, A11 etc.
instead of
A1, A2, A3, etc.

I tried to set the 'order by' property to someting like 'X,val(Y)' but
the only result is that I get a random result order. Can anybody help
me, I'm rather desperate on this.

The version is Access 2002, SP3

Thanks for any input,
Stephan

 

[Douglas J. Steele]

It looks as though I may have misread it, and you were right: John agrees
with you, not me. <g>

Steph: You may need to set the form's OrderByOn property to True, in
addition to setting the OrderBy property.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I agree with what you say, but I was understanding from the poster that X
and Y were separate strings, not "parts" of a single string. If his setup
is the latter, then yes (no? <g>), my answer isn't right.

--

Ken Snell
<MS ACCESS MVP>

Sorry, Ken, but that won't work.

Val stops reading the string at the first character it can't recognize as
part of a number, and since the values start with letters, that means it
won't return anything.

If it's always a single letter, you could use ORDER BY [X],
Val(Mid([Y],2))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This should work for you:

ORDER BY [X], Val([Y])

Tell us more about the SQL statement in its entirety. Any chance that Y
would be empty or a nonumeric value?

--

Ken Snell
<MS ACCESS MVP>



Hi,

I have a form that displays data from an external oracle-table. I have
2 coordinate fields in this table, namely X and Y, hey hold values like
X='A', Y='5', similar to the fields on a chess board.

I want to order my query results according to the coordinates like
'ORDER BY X,Y'. The problem is Y is a character field instead of a
number field so the resulting order looks like:
A1, A10, A11 etc.
instead of
A1, A2, A3, etc.

I tried to set the 'order by' property to someting like 'X,val(Y)' but
the only result is that I get a random result order. Can anybody help
me, I'm rather desperate on this.

The version is Access 2002, SP3

Thanks for any input,
Stephan

 

[Ken Snell [MVP]]

That's alright... I missed the part about trying to use a form's OrderBy
property....

--

Ken Snell
<MS ACCESS MVP>

It looks as though I may have misread it, and you were right: John agrees
with you, not me. <g>

Steph: You may need to set the form's OrderByOn property to True, in
addition to setting the OrderBy property.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I agree with what you say, but I was understanding from the poster that X
and Y were separate strings, not "parts" of a single string. If his setup
is the latter, then yes (no? <g>), my answer isn't right.

--

Ken Snell
<MS ACCESS MVP>

Sorry, Ken, but that won't work.

Val stops reading the string at the first character it can't recognize
as part of a number, and since the values start with letters, that means
it won't return anything.

If it's always a single letter, you could use ORDER BY [X],
Val(Mid([Y],2))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



This should work for you:

ORDER BY [X], Val([Y])

Tell us more about the SQL statement in its entirety. Any chance that Y
would be empty or a nonumeric value?

--

Ken Snell
<MS ACCESS MVP>



Hi,

I have a form that displays data from an external oracle-table. I have
2 coordinate fields in this table, namely X and Y, hey hold values
like
X='A', Y='5', similar to the fields on a chess board.

I want to order my query results according to the coordinates like
'ORDER BY X,Y'. The problem is Y is a character field instead of a
number field so the resulting order looks like:
A1, A10, A11 etc.
instead of
A1, A2, A3, etc.

I tried to set the 'order by' property to someting like 'X,val(Y)' but
the only result is that I get a random result order. Can anybody help
me, I'm rather desperate on this.

The version is Access 2002, SP3

Thanks for any input,
Stephan

 

[steph]

After having experimented with the form's order-by attribute without
having success I've tried this solution and it worked fine for me. My
select-statement now looks like this:

SELECT SAMPLES.SAM_ID, SAMPLES.PLA_ID, SAMPLES.X_COOR, SAMPLES.Y_COOR,
Val(IIf(IsNull([Y_COOR]),"0",([Y_COOR]))) AS SortY, SAMPLES.SOLUBLE
FROM SAMPLES;

Thanks a lot,
Stephan