Sorting records

[sven2]

Hello,

I have made A query to display some records order by date and hour.

SELECT cursusplanning.Planningsnummer, cursusplanning.Cursusdatum, cursus_stam.Cursusnaam, cursusplanning.Beginuur, cursusplanning.Einduur
FROM cursus_stam INNER JOIN cursusplanning ON cursus_stam.Cursusnummer = cursusplanning.Cursusnummer
WHERE (((cursusplanning.Cursusdatum)>=[Forms]![FrmPlannenCursusFilteren]![txtbeginperiode] And (cursusplanning.Cursusdatum)<=[Forms]![FrmPlannenCursusFilteren]![txteindperiode]))
ORDER BY cursusplanning.Cursusdatum, cursusplanning.Beginuur;

This is working like it should except the order by hour??

for example:

24/11/2007 16u - 18u
24/11/2007 14u - 17u

why is it that my query put 16u before 14u ???

Thanks in advance,
Sven.

 

[Colin@Toyota]

16u - 18u
14u - 17u

are these numbers in a single field [Beginuur]?

 

[sven2]

yes, they are in a single field

 

[Colin@Toyota]

yes, they are in a single field

so it is a text field then?

try changing the last part of the line to:
cursusplanning.Beginuur DESC;

 

[sven2]

No, this is also not working ...

to answer your question: it is a short timefield

 

[Colin@Toyota]

it is a short timefield

Ok, so what is the purpose of the field? Is it showing a time period for each record? I don't understand the 16u - 18u...

Will the times put in this field always be whole hours? No minutes recorded?

 

[sven2]

Hello,

the purpose:

what I do is for each course I give the date, starttime and endtime.
The start and endtime are always whole hours.

Now I want to make a query that gives me for a time period these courses and when there are moore than 1 course in a day they should be sorted like the example.

For example:

course: date: starttime (begintijd)

course 1 21/11/2006 14u
course 2 21/11/2006 17u
course 3 21/11/2006 20u

 

[Colin@Toyota]

try switching the order the fields appear in the last line...

so it would look like:
ORDER BY cursusplanning.Beginuur, cursusplanning.Cursusdatum;

 

[Rich]

What time does 14u represent?

 

[sven2]

Hello,

if I do it that way, the starttime is sorted correctly but the dates are not sorted properly.

 

[Colin@Toyota]

What time does 14u represent?

I was wondering the same thing... I assumed 24 hour clock... so 2:00pm?

if I do it that way, the starttime is sorted correctly but the dates are not sorted properly.

Maybe try

GROUP BY cursusplanning.Cursusdatum
ORDER BY cursusplanning.Beginuur;

I'm running out of ideas...

 

[sven2]

indeed, 14u is 2 pm

 

[sven2]

Hello,

you never believe it but the group by did the trick!
Issue solved ... yes!

Thanks a lot for the help!!!!

Best regards,
Sven.

 

[Colin@Toyota]

Whew!
I'm just glad I could finally help...