Bizarre "Order By" problem

[J.Windebank]

I have a query that goes through a list of TV programs, and puts them in order by Time, and then by Channel. The channels are sorted by their ID number, 1, 2, 3, 4 or 5. This works fine to put things in order, however I have come across a situation where I need to change the sort order a little.

I need it to sort by channels in the order: 1, 2, 4, 3, 5.

Is there anyway I can do this, or am I in trouble?

 

[J.Windebank]

OK, I have realised I can use an Iif statement in the query to change a value of 3 in the ChannelID to a value of 6, this will put it in the correct order for me.

Now, ina normal query, this works fine, and displays exactly as I want it too, though am having trouble using it inside a VBA function.

Here is the code, can anyone help me debug it please?

If strgblStateID = 3 Then
     strsql = "SELECT tblMain.COTRound, tblStates.StateID, tblMain.DayID, tblTimeFrame.TimeFrame, tblTimes.Time, tblPrograms.ProgramName, tblPrograms.ProgramCode, tblChannels.ChannelName, tblStates.State, IIf([tblPrograms].[ChannelID]=3,6,[tblPrograms].[ChannelID]) AS TasChannelID " _
            & "FROM tblDay INNER JOIN ((tblChannels INNER JOIN tblPrograms ON tblChannels.ChannelID = tblPrograms.ChannelID) INNER JOIN (tblTimes INNER JOIN (tblTimeFrame INNER JOIN (tblStates INNER JOIN tblMain ON tblStates.StateID = tblMain.StateID) ON tblTimeFrame.TimeFrameID = tblMain.TimeframeID) ON tblTimes.TimeID = tblMain.TimeID) ON tblPrograms.ProgramID = tblMain.ProgramID) ON tblDay.DayID = tblMain.DayID " _
            & "WHERE (((tblMain.COTRound)='" & [strgblCOTRound] & "') AND ((tblMain.DayID)=" & [strgblDayID] & ") AND ((tblMain.StateID)=" & [strgblStateID] & ") AND ((tblTimeFrame.TimeFrameID)=1)) " _
            & "ORDER BY tblTimeFrame.TimeFrameID, tblTimes.Time, TasChannelID;"
Else
     strsql = "SELECT tblMain.COTRound, tblStates.StateID, tblMain.DayID, tblTimeFrame.TimeFrame, tblTimes.Time, tblPrograms.ProgramName, tblPrograms.ProgramCode, tblChannels.ChannelName, tblStates.State, tblPrograms.ChannelID  " _
            & "FROM tblDay INNER JOIN ((tblChannels INNER JOIN tblPrograms ON tblChannels.ChannelID = tblPrograms.ChannelID) INNER JOIN (tblTimes INNER JOIN (tblTimeFrame INNER JOIN (tblStates INNER JOIN tblMain ON tblStates.StateID = tblMain.StateID) ON tblTimeFrame.TimeFrameID = tblMain.TimeframeID) ON tblTimes.TimeID = tblMain.TimeID) ON tblPrograms.ProgramID = tblMain.ProgramID) ON tblDay.DayID = tblMain.DayID " _
            & "WHERE (((tblMain.COTRound)='" & [strgblCOTRound] & "') AND ((tblMain.DayID)=" & [strgblDayID] & ") AND ((tblMain.StateID)=" & [strgblStateID] & ") AND ((tblTimeFrame.TimeFrameID)=1)) " _
            & "ORDER BY tblTimeFrame.TimeFrameID, tblTimes.Time, tblPrograms.ChannelID;"
End If

Thanks,

Jordan

Edit: Hope the table is not too wide, tried to cut the lines down further but it was becoming too hard to understand.

 

[J.Windebank]

Bump before bed? ^

Hope someone here can understand my dillema.

 

[Pat Hartman]

Add a column to the table to represent the alternate sort order and sort by the new column.

 

[J.Windebank]

My lord, what in earth was I thinking!?!?

Such an easy fix, don't I feel stupid now

Thank you Pat, thank you heaps