Strange date sort problem

[Christine Pearc]

I have a form that allows the user to specify, among other things, date ranges for data to be displayed in a subform (in a form, not datasheet).

In the subform, the user can click any column heading to sort the records by record number, employee name, department, etc. The Click event calls a function:

Private Sub lblReviewDate_Click()
    Call SortForm(Me, "ReviewDate")
End Sub

Here's the function module code:

Function SortForm(frm As Form, ByVal sOrderBy As String) As Boolean
    'Sort form column headings OrderBy to the string. Reverse if already set. 
    If Len(sOrderBy) > 0 Then
        ' Reverse the order if already sorted this way.
        If frm.OrderByOn And (frm.OrderBy = sOrderBy) Then
            sOrderBy = sOrderBy & " DESC"
        End If
        frm.OrderBy = sOrderBy
        frm.OrderByOn = True
        SortForm = True
     End If
End Function

This works great - except on date fields, which are set to the medium date format as DD-Mmm-YY. I end up with a sort list that looks like this:

21-Apr-05
24-Jun-05
29-Jun-05
11-Jul-05
05-Apr-05
07-Jun-05​

A sort in the reverse direction is equally messed up. Can someone advise what to do about this?

Many thanks
Christine

 

[Mile-O]

Don't sort on a formatted date as a formatted date is text.

 

[Christine Pearc]

Boy, that was fast... Thanks, SJ! But, er... I understand that it's not good to store date info as text. I don't understand what the point of date fields is if you can't do something with them like this. Sorting dates is quite important ot my application. Do I need to create other fields to store date info as text? Somehow extract parts of the date (which would then, I guess require some type of If statement in my function, like If it's a date, then do something with it (what?), etc?

Confused,
Christine

 

[Mile-O]

You are not sorting on a date field, you are sorting on a text representation of the date field. Take the Medium Date format off.

 

[Christine Pearc]

Oh, I see!

I took off the formatting and sorting works. However, I'm an American in England and my simple mind still gets confused looking at the D/M/YR format used here, so I really want that formatted date.

As a work-around, I've since discovered CDate. In my query, I've added

DateReviewed: CDate([ReviewDate])

, placed a hidden control with DateReviewed on the subform, and changed the function to sort on DateReviewed instead of the formatted date field.

This works great, but SJ, one last question if I may: Is this solution good programming I seem to have to use alot of workarounds to get what I want and am afraid my less-than-elegant newbie solutions will ultimately bloat and slow down my apps.

As always, thanks for your help,
Christine

 

[ScottGem]

Just to expand a bit here. You need to understand how Access stores dates. Dates are stored as a serial number counting the days from 12/30/1899. How a date appears on forms and reports is a function of formatting but does not change the value stored in the field. So if you do your formatting in the wrong places, you could be sorting on a text representation of the date and the sort will be based on the characters displayed.

 

[Christine Pearc]

Scott and Pat,
Thanks for your ideas and info. I haven't formatted the date in the query, but the format IS specified as a medium date (DD/Mmm/YR) in the table, where an option to select 'no formatting' isn't available. So, am I assuming correctly that the only way to sort dates is as I've done iusing CDate?

Regards,
Christine

 

[Mile-O]

There's no need to format the data in the table - you will display your data via forms anyway.

 

[ScottGem]

Just delete the format in the table leaving the box blank.