Sorting by Date

[PC User]

I have a subform that simulates a listbox and I click on the header label to activate the sort. My problem is sorting by date. Below is the sort function and one of the date labels that I need to sort on.

Sort Function:Code:
==========================================
Private Function SortOrder(col As String, xorder As String) As Integer
Dim strSQL As String
Dim sf As Form
Set sf = Forms!frmMainEntry!fctlNotifications.Form
strSQL = "SELECT DISTINCTROW ProgramID, ProgramDescription, Facility, ResponsibleParty, DueDate, FrequencyOfService, AdvancedNoticeDate "
strSQL = strSQL & "FROM qryProgramList "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
sf.RecordSource = strSQL
sf.Form.Requery
End Function
==========================================

On-Click Date:Code:
==========================================
Private Sub lblDueDate_Click()
Dim response As Integer
If Me.txtSortOrder = "DESC" Then
response = SortOrder(CDate(DueDate), "asc")
Me.txtSortOrder = "asc"
Else
response = SortOrder(CDate(DueDate), "DESC")
Me.txtSortOrder = "DESC"
End If
End Sub
==========================================
I'm not sure if I should convert the date into something that can be sorted or just leave it as a date. I can't get it to work. Help please.

Thanks,
PC

 

[pbaldy]

I would think you'd need to pass the name of the field there, not its value.

 

[PC User]

I'm not sure what you mean. Can you clarify your idea? I'm attaching the sample that I found on the internet. I added the date field to show you my problem on the subform.

Thanks,
PC

 

[pbaldy]

This line

strSQL = strSQL & "ORDER BY " & col & " " & xorder

is expecting a field name, not an actual date. Try:

response = SortOrder("DueDate", "asc")

 

[PC User]

That's the way it shows in the example I uploaded. I didn't use the date conversion and it still doesn't work. I used the format from the example first, then I tried the date conversion and neither attempt worked. Could that be a problem between A97 and A2K? I can't seem to trace the error.

~~PC

 

[pbaldy]

Is there a form in the sample that fails? The date sort in SubformExample works fine (except for glitch in the captions). I searched for the specific code you posted earlier, and it doesn't exist in the sample.

 

[ChrisO]

G’day Paul, how’s it going.

This sort of thing starts to become annoying after a while.

PC User, can you let both sites know the outcome when you find it?

Regards,
Chris.

 

[pbaldy]

I concur, my friend.

I'm doing well, hope you are too.

 

[ChrisO]

Yep, quite reasonable…BTW, what are you working on at the moment…no point in both of us doing it?

ETA…
Sorry I forgot to add…it’s really hot here, might have to go to the local for a cold one.

 

[PC User]

I think I've found the problem. In the query there's a calculated field AdvancedNoticeDate: DateAdd("ww",-2,[DueDate]) that gives an error when the [DueDate] is blank. It may be that the sort function is trying to sort on erroneous data (ie. #Error). I'll need a solution for the case when no due date is given.

PC

 

[pbaldy]

Actually Mr. Wise Guy, it was pretty nice here today, so your weather cracks aren't as effective today.

What am I working on where? On the boards, nothing at the moment (playing poker in another window at the moment). At work, just finishing converting a pretty complex VBA process (20 pages printed out) into T-SQL on SQL Server. Just about have it, and it runs 20 TIMES faster. Pretty happy with it.

 

[PC User]

Sorry if you misunderstood my comments. I didn't mean to offend you. Please have a nice day. You may see my posting in other forums, because it increases the possibility of me getting an answer and there are seperate websites.

PC

 

[pbaldy]

PC User, my "wise guy" comment was directed at ChrisO, who likes to brag about how nice his weather is when mine is bad. You did not offend me.

Having said that, I understand the motivation to post your question all over the place. Can you understand the frustration of spending time working out an answer for someone, only to find out that the question was answered on some other site earlier. It makes you feel like you've wasted your time. Once that happens a few times to you, you get edgy about it. That's where Chris and I are coming from.

 

[PC User]

After correcting the formulae in the calculated dates, I discovered that the sort function does work on dates. I will show you the errors in the formulae by displaying the before and after code.

Before Code:
==========================================
AdvancedNoticeDate: DateAdd("ww",-2,[DueDate])

DueDate: Format(IIf([tsubProgramList]![FrequencyOfService]="Annually",DateAdd("yyyy",1,[tsubProgramList]![InitialDate]),IIf([tsubProgramList]![FrequencyOfService]="Semiannually",DateAdd("m",6,[tsubProgramList]![InitialDate]),IIf([tsubProgramList]![FrequencyOfService]="Quarterly",DateAdd("q",1,[tsubProgramList]![InitialDate]),IIf([tsubProgramList]![FrequencyOfService]="Monthly",DateAdd("m",1,[tsubProgramList]![InitialDate]),IIf([tsubProgramList]![FrequencyOfService]="Three Years",DateAdd("yyyy",3,[tsubProgramList]![InitialDate]),IIf([tsubProgramList]![FrequencyOfService]="Five Years",DateAdd("yyyy",5,[tsubProgramList]![InitialDate]),0)))))),"mm\/dd\/yyyy")

==========================================

After Code:
==========================================
AdvancedNoticeDate: IIf(IsNull([DueDate]),Null,DateAdd("ww",-2,[DueDate]))

DueDate: DateAdd("m",Switch([FrequencyOfService]="Annually",12,[FrequencyOfService]="Semiannually",6,[FrequencyOfService]="Quarterly",3,[FrequencyOfService]="Monthly",1,[FrequencyOfService]="Three Years",36,[FrequencyOfService]="Five Years",60,[FrequencyOfService]="None",0),[InitialDate])
==========================================

 

[PC User]

The corrected sort function and event code is as follows

Sort Function Code:
===================================================
Private Function SortOrder(col As String, xorder As String) As Integer
Dim strSQL As String
Dim sf As Form
'Set sf = Forms!frmMainEntry!fsubNotifications.Form 'This is the subform
Set sf = Forms!frmMainEntry!fctlNotifications.Form 'This is the subform control

strSQL = "SELECT DISTINCTROW ProgramID, ProgramDescription, Facility, ResponsibleParty, DueDate, FrequencyOfService, AdvancedNoticeDate "
strSQL = strSQL & "FROM qryProgramList "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
sf.RecordSource = strSQL
sf.Form.Requery
End Function
===================================================

On-Click Date Code:
===================================================
Private Sub lblDueDate_Click()
Dim response As Integer
If Me.txtSortOrder = "DESC" Then
response = SortOrder(“DueDate”, "asc")
Me.txtSortOrder = "asc"
Else
responresponse = SortOrder(“DueDate”, "DESC")
Me.txtSortOrder = "DESC"
End If
End Sub
===================================================