Chronological Order

[Dscalf1]

I am trying to figure how to get some information to show up in chronological order.

For example,
It is showing up like this..

Week 1
Week 10
Week 2
Week 20
Week 3
Week 4
and so on


I want it like week 1
week 2
week 3
week 4
week 10
week 20
Anyway i can do this? please help

 

[Gasman]

Get rid of the text "Week" and sort on the rest?

 

[plog]

When you don't store data correctly, this happens. If everything in that field is prefixed with 'Week ', then you don't need the 'Week ' part stored and you can store your numbers like numbers (not as text as you have now) and you can work with those numbers like numbers and have them sort correctly. Right now, because its text, its sorting your data as text ("100" comes after "1" but before "2")

So my main advice to you is to store your data properly. Numbers should be stored as numbers, text as text and any data not necessary--not stored at all. Reconfigure your table to store your data in that manner.

With that said, you can extract the numeric part of the data using Mid (http://www.techonthenet.com/access/functions/string/mid.php) and InStr(http://www.techonthenet.com/access/functions/string/instr.php)

 

[CJ_London]

or store as

week 01
week 02
week 10

 

[Dscalf1]

Thanks everyone I am coming into the access database I did not create it so I was just wondering if there was an easier way instead of having to go change everything already in there.. But I see that I will have to thanks again!

 

[MarkK]

You could try this in a query . . .

SELECT t.* FROM 
    (
    SELECT WeekField, CLng(Split(WeekField)(1)) As WeekNumber
    FROM tYourWeekTable
    ) As t
ORDER BY t.WeekNumber;

. . . but it'll choke if you have data like . . .

Week10

. . . where there is no space between "Week" and the following number.

Maybe this would work better . . .

SELECT t.* FROM 
    (
    SELECT WeekField, CLng(Trim(Replace(WeekField, "Week", ""))) As WeekNumber
    FROM tYourWeekTable
    ) As t
ORDER BY t.WeekNumber;

. . . but as others have observed, these are all work-arounds for poorly stored data.

 

[CJ_London]

I didn't think you could use the split function in sql - and don't you have to specify the character on which to split?

 

[pbaldy]

I didn't think you could use the split function in sql - and don't you have to specify the character on which to split?

I didn't either, and in a brief test I get an error trying to use it in SQL. However, the delimiter is optional and the default is a space.

 

[MarkK]

No, I can't get Split() to work in SQL either. So cancel that.