| Author |
 Topic  |
|
Classicmotorcycling
Development Team Leader
Australia
2084 Posts |
 Posted - 02 January 2005 : 19:21:53
|
Hello,
I have searched for this and found nothing on it, but that could just be me. 
I need to be able to sort some fields to come out on a asp page to be sorted by not only the time it starts, but also the day and would like Monday to be the first day. Can anyone help me with this?
Here is my SQL string that I can sort with one field, but can not get the days to sort by Monday, Tuesday, Wednesday, Thursday, Friday, Saturday & Sunday:
strSQL = "SELECT PIVOT_NEW_SEMESTER_DETAILS.* FROM PIVOT_NEW_SEMESTER_DETAILS ORDER BY New_Semester_Time_B ASC;" That allows the code to sort by the New_Semester_Time_B field, but I need to also sort by day of the week from the following field: New_Semester_Day which I need to start on the Monday.
Any push in the right direction would be fantastic if you can understand what I am asking. 
|
Cheers,
David Greening |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 02 January 2005 : 19:53:38
|
| Just for clarification, are the days of the week in the database in date format or actually Monday, Tuesday, etc...? (Inserting values like this into a database is not the preferred method.) Are you willing to create another field for day sort order? You might need to dump the recordset into an array and then get the day number for the days and resort the array. I'll see what other options you have. |
 |
 |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 02 January 2005 : 20:01:45
|
| Well, I know a way I can get this accomplished for you but it will require the array method or creating a memory recordset in which I could then re-order. I'd like to give this topic a bit of time for other input before I share anything with you though. |
|
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 02 January 2005 : 20:27:27
|
Somthing like this maybe?
strSQL = "SELECT PIVOT_NEW_SEMESTER_DETAILS.* FROM PIVOT_NEW_SEMESTER_DETAILS GROUP BY DATENAME(dw,New_Semester_Day) ORDER BY New_Semester_Time_B ASC;" |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 02 January 2005 : 20:28:41
|
| What database are you using? With Access you could try the WeekDay function to create a sort by field. In SQL Server, you could use DATEPART for the same effect. The only issue is that both consider Sunday to be day 1, not Monday, though that can be change in SQL Server, through the use of SET DATEFIRST. Also I don't know if WeekDay can be used outside of Access, though I suspect it can't. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 02 January 2005 : 20:45:07
|
I don't think his day of the week field is a date field so Weekday function won't work, unless I am missing something. However, had it been a date field you can pass an additional parameter of the first day of the week.
Weekday ( date_value, [firstdayofweek] ) |
|
Edited by - dayve on 02 January 2005 20:45:32 |
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2084 Posts |
Posted - 02 January 2005 : 20:50:12
|
Sorry guys, I should of said it is using Access 2000 and the day names are added as you see them, Monday, Tuesday, etc...?
D3mon,
Tried your code:quote:
Originally posted by D3mon
Somthing like this maybe?
strSQL = "SELECT PIVOT_NEW_SEMESTER_DETAILS.* FROM PIVOT_NEW_SEMESTER_DETAILS GROUP BY DATENAME(dw,New_Semester_Day) ORDER BY New_Semester_Time_B ASC;"
and got:Microsoft JET Database Engine error '80040e21'
Cannot group on fields selected with '*' (PIVOT_NEW_SEMESTER_DETAILS). Any ideas?
|
Cheers,
David Greening |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 02 January 2005 : 21:04:10
|
| just replace PIVOT_NEW_SEMESTER_DETAILS.* part with a comma seperated list of the actual field names you want retrieved (including New_Semester_Day, of course.) |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 02 January 2005 : 21:11:16
|
| the DateName function does not work in MS Access and requires a date field. from everything being said, it sounds like the days of the week are in a text field in the database. |
|
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2084 Posts |
Posted - 02 January 2005 : 21:17:40
|
Added the comma seperated values to the sting:
strSQL = "SELECT PIVOT_NEW_SEMESTER_DETAILS.New_Semester_Group, PIVOT_NEW_SEMESTER_DETAILS.New_Semester_Day, PIVOT_NEW_SEMESTER_DETAILS.New_Semester_Time_B, PIVOT_NEW_SEMESTER_DETAILS.New_Semester_Time_E, PIVOT_NEW_SEMESTER_DETAILS.New_Semester_Cost FROM PIVOT_NEW_SEMESTER_DETAILS GROUP BY DATENAME(dw,New_Semester_Day) ORDER BY New_Semester_Time_B ASC;" Now I get the follwoing error:Microsoft JET Database Engine error '80040e14'
Undefined function 'DATENAME' in expression. What sort of fuction should I have for it. Keep in mind, this is not for a Snitz related page.
|
Cheers,
David Greening |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 02 January 2005 : 21:17:44
|
ah, had assumed this was a date-type field. Datepart would do for Access, then using ASP to convert the number using WeekdayName(WeekDay, Abbreviate, FirstDayofWeek) function
CM, can you identify what kind of data the new_semester_day column holds? |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
Edited by - D3mon on 02 January 2005 21:19:48 |
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2084 Posts |
Posted - 02 January 2005 : 21:19:16
|
Correct, The days of ther week are a text field.quote:
Originally posted by dayve
the DateName function does not work in MS Access and requires a date field. from everything being said, it sounds like the days of the week are in a text field in the database.
|
Cheers,
David Greening |
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2084 Posts |
Posted - 02 January 2005 : 21:22:09
|
D3mon,
The field is a text field.quote:
Originally posted by D3mon
CM, can you identify what kind of data the new_semester_day column holds?
|
Cheers,
David Greening |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 02 January 2005 : 21:22:24
|
Oh dear. that's not good at all. Quick and dirty fix might be to use a query for each day of the week (means 7 calls to the DB though!)
strSQL = "... WHERE new_semester_day = 'monday'" etc.. |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 02 January 2005 : 21:29:43
|
quote:
Originally posted by D3mon
Oh dear. that's not good at all. Quick and dirty fix might be to use a query for each day of the week (means 7 calls to the DB though!)
strSQL = "... WHERE new_semester_day = 'monday'" etc..
I have a better method than that which will not put such a burden on the database (using the memory recordset).
I have family over right now, but I can give you the code needed to accomplish this tomorrow. |
|
Edited by - dayve on 02 January 2005 21:30:01 |
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2084 Posts |
Posted - 02 January 2005 : 21:30:58
|
No it's not good  .. I was hoping it would be a simple SQL call to sort the New_Semester_Day column then sort the New_Semester_Time_B column, but it is not looking that easy.
I just need to be able to select a day of the week when I add it to the database, and for it to display in order of Day of the Week and then by it's beginning time. If there is way of doing it with different DB fields and a new call, then I can do it as the site is only in development at present. |
Cheers,
David Greening |
|
|
|
Topic |
|
Topic Locked
