Sorting Numeric Values in a Text Field

[Guest]

I have created a query that reads an external dBASEIII file. The field I want
to sort on contains numeric values. However the dBASE file has the field
defined as "text".
My query returns a sort which is like the following"
1
10
11
13
2
3
4....
Can I correct this so that my query is in the correct numeric sequence?

Thanks
Michael

 

[Jeff Boyce]

If you coerce the text into a numeric value (e.g., CInt([YourField]), you
can sort THAT.

Or you might try the Val() function (see Access HELP for either...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Snell \(MVP\)]

SELECT *
FROM TableName
ORDER BY Val(NumberField);

 

[John W. Vinson]

I have created a query that reads an external dBASEIII file. The field I want
to sort on contains numeric values. However the dBASE file has the field
defined as "text".
My query returns a sort which is like the following"
1
10
11
13
2
3
4....
Can I correct this so that my query is in the correct numeric sequence?

Thanks
Michael

Sort by Val([fieldname]).

John W. Vinson [MVP]

 

[Marshall Barton]

I have created a query that reads an external dBASEIII file. The field I want
to sort on contains numeric values. However the dBASE file has the field
defined as "text".
My query returns a sort which is like the following"
1
10
11
13
2
3
4....
Can I correct this so that my query is in the correct numeric sequence?

Will sorting on Val(thefield) work in that environment?

 

[Guest]

This worked and my sort is looking correct. However my field CUM_DIS is not
in the correct sort sequence.

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1, (SELECT SUM(Backspan_1)
FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) AS Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [ ENTER
START ] And [ENTER END ]))
ORDER BY Val(TERMINAL_1);

Below is a sample from the query:

ROUTE_1 TERMINAL_1 BACKSPAN_1 Cum_Dist
10B-14L 3 21 540
10B-14L 4 293 833
10B-14L 5 634 1467
10B-14L 6 455 1922
10B-14L 7 577 2499
10B-14L 8 322 2821
10B-14L 9 302 3123
10B-14L 10 267 267
10B-14L 11 252 519

Thanks for the help
Michael

I have created a query that reads an external dBASEIII file. The field I want
to sort on contains numeric values. However the dBASE file has the field
defined as "text".
My query returns a sort which is like the following"
1
10
11
13
2
3
4....
Can I correct this so that my query is in the correct numeric sequence?

Thanks
Michael

Sort by Val([fieldname]).

John W. Vinson [MVP]

 

[Douglas J. Steele]

That's probably because you haven't included it in your ORDER BY clause.

ORDER BY Val(TERMINAL_1), 4


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This worked and my sort is looking correct. However my field CUM_DIS is
not
in the correct sort sequence.

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1, (SELECT
SUM(Backspan_1)
FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) AS Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [
ENTER
START ] And [ENTER END ]))
ORDER BY Val(TERMINAL_1);

Below is a sample from the query:

ROUTE_1 TERMINAL_1 BACKSPAN_1 Cum_Dist
10B-14L 3 21 540
10B-14L 4 293 833
10B-14L 5 634 1467
10B-14L 6 455 1922
10B-14L 7 577 2499
10B-14L 8 322 2821
10B-14L 9 302 3123
10B-14L 10 267 267
10B-14L 11 252 519

Thanks for the help
Michael

I have created a query that reads an external dBASEIII file. The field I
want
to sort on contains numeric values. However the dBASE file has the field
defined as "text".
My query returns a sort which is like the following"
1
10
11
13
2
3
4....
Can I correct this so that my query is in the correct numeric sequence?

Thanks
Michael

Sort by Val([fieldname]).

John W. Vinson [MVP]

 

[Guest]

Are you saying that I should now have the following? I have tried this and it
still does not list CUM_DIS in the correct sequence.
Thanks for your assistance.

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1, (SELECT SUM(Backspan_1)
FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) AS Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [ ENTER
START ] And [ENTER END ]))
ORDER BY Val([TERMINAL_1]), 4;

That's probably because you haven't included it in your ORDER BY clause.

ORDER BY Val(TERMINAL_1), 4


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This worked and my sort is looking correct. However my field CUM_DIS is
not
in the correct sort sequence.

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1, (SELECT
SUM(Backspan_1)
FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) AS Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [
ENTER
START ] And [ENTER END ]))
ORDER BY Val(TERMINAL_1);

Below is a sample from the query:

ROUTE_1 TERMINAL_1 BACKSPAN_1 Cum_Dist
10B-14L 3 21 540
10B-14L 4 293 833
10B-14L 5 634 1467
10B-14L 6 455 1922
10B-14L 7 577 2499
10B-14L 8 322 2821
10B-14L 9 302 3123
10B-14L 10 267 267
10B-14L 11 252 519

Thanks for the help
Michael

On Mon, 25 Jun 2007 11:57:01 -0700, MValentine

I have created a query that reads an external dBASEIII file. The field I
want
to sort on contains numeric values. However the dBASE file has the field
defined as "text".
My query returns a sort which is like the following"
1
10
11
13
2
3
4....
Can I correct this so that my query is in the correct numeric sequence?

Thanks
Michael

Sort by Val([fieldname]).

John W. Vinson [MVP]

 

[John Spencer]

How do you want the records sorted? The sample you posted is Sorted by
Terminal_1.

If you want to sort by Cum_Dist from largest to smallest value, then you
need to change the order by clause. Perhaps to

ORDER BY 4 DESC , Val(Terminal_1)

or to the more complex

ORDER BY (SELECT SUM(Backspan_1)
FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) ,
Val(Terminal_1)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Are you saying that I should now have the following? I have tried this and
it
still does not list CUM_DIS in the correct sequence.
Thanks for your assistance.

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1, (SELECT
SUM(Backspan_1)
FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) AS Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [
ENTER
START ] And [ENTER END ]))
ORDER BY Val([TERMINAL_1]), 4;

That's probably because you haven't included it in your ORDER BY clause.

ORDER BY Val(TERMINAL_1), 4


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This worked and my sort is looking correct. However my field CUM_DIS is
not
in the correct sort sequence.

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1, (SELECT
SUM(Backspan_1)
FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) AS Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [
ENTER
START ] And [ENTER END ]))
ORDER BY Val(TERMINAL_1);

Below is a sample from the query:

ROUTE_1 TERMINAL_1 BACKSPAN_1 Cum_Dist
10B-14L 3 21 540
10B-14L 4 293 833
10B-14L 5 634 1467
10B-14L 6 455 1922
10B-14L 7 577 2499
10B-14L 8 322 2821
10B-14L 9 302 3123
10B-14L 10 267 267
10B-14L 11 252 519

Thanks for the help
Michael

:

On Mon, 25 Jun 2007 11:57:01 -0700, MValentine

I have created a query that reads an external dBASEIII file. The
field I
want
to sort on contains numeric values. However the dBASE file has the
field
defined as "text".
My query returns a sort which is like the following"
1
10
11
13
2
3
4....
Can I correct this so that my query is in the correct numeric
sequence?

Thanks
Michael

Sort by Val([fieldname]).

John W. Vinson [MVP]

 

[Guest]

I actually found a way to solve my problem. My primary objective was to sort
and list by terminal all features associated to a particular route and
display the backspan for each feature found. From that I was wanting to show
a progressive cumulative distance based on the backspan. I ended up createing
a query which found and listed the features in the correct order. I then
created a report based on the query and used the RunningSum function to
obtain the cumulative distance. It worked. I got just what I was needing.
Don't know if that is the proper way if doing it but it got what I needed.
Thanks for the assistance.

How do you want the records sorted? The sample you posted is Sorted by
Terminal_1.

If you want to sort by Cum_Dist from largest to smallest value, then you
need to change the order by clause. Perhaps to

ORDER BY 4 DESC , Val(Terminal_1)

or to the more complex

ORDER BY (SELECT SUM(Backspan_1)
FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) ,
Val(Terminal_1)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Are you saying that I should now have the following? I have tried this and
it
still does not list CUM_DIS in the correct sequence.
Thanks for your assistance.

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1, (SELECT
SUM(Backspan_1)
FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) AS Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [
ENTER
START ] And [ENTER END ]))
ORDER BY Val([TERMINAL_1]), 4;

That's probably because you haven't included it in your ORDER BY clause.

ORDER BY Val(TERMINAL_1), 4


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


This worked and my sort is looking correct. However my field CUM_DIS is
not
in the correct sort sequence.

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1, (SELECT
SUM(Backspan_1)
FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) AS Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [
ENTER
START ] And [ENTER END ]))
ORDER BY Val(TERMINAL_1);

Below is a sample from the query:

ROUTE_1 TERMINAL_1 BACKSPAN_1 Cum_Dist
10B-14L 3 21 540
10B-14L 4 293 833
10B-14L 5 634 1467
10B-14L 6 455 1922
10B-14L 7 577 2499
10B-14L 8 322 2821
10B-14L 9 302 3123
10B-14L 10 267 267
10B-14L 11 252 519

Thanks for the help
Michael

:

On Mon, 25 Jun 2007 11:57:01 -0700, MValentine

I have created a query that reads an external dBASEIII file. The
field I
want
to sort on contains numeric values. However the dBASE file has the
field
defined as "text".
My query returns a sort which is like the following"
1
10
11
13
2
3
4....
Can I correct this so that my query is in the correct numeric
sequence?

Thanks
Michael

Sort by Val([fieldname]).

John W. Vinson [MVP]