Sorting Numeric Values in a Text Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
Click to expand...

Sort by Val([fieldname]).

John W. Vinson [MVP]
 
MValentine said:
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?
Click to expand...


Will sorting on Val(thefield) work in that environment?
 
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

John W. Vinson said:
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
Click to expand...

Sort by Val([fieldname]).

John W. Vinson [MVP]
Click to expand...
 
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)


MValentine said:
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

John W. Vinson said:
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
Click to expand...

Sort by Val([fieldname]).

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
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;

Douglas J. Steele said:
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)


MValentine said:
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

John W. Vinson said:
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]
Click to expand...
Click to expand...
Click to expand...
 
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
..

MValentine said:
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;

Douglas J. Steele said:
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)


MValentine said:
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]
Click to expand...
Click to expand...
Click to expand...
 
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.

John Spencer said:
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
..

MValentine said:
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;

Douglas J. Steele said:
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]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sorting a calculated numeric field 2
Split data in field on character return 0
Numeric Field Overflow 1
Sorting of text to number conversion 2
Numeric Field Overflow Error 5
How stop Append from sorting records? 3
Converting a numeric field to text 1
Force text field to numeric field? 2

Back
Top