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

 

[Pat Hartman]

I think the problem is probably that you formatted the date in the query rather than in the form. If you remove the formatting from the query, the date field will remain a date and therefore will sort properly. You can still display it as you like on the form.

 

[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.