asked on

MSHflexgrid date format problem

I am using ADO to access MSAccess and/or mysql and create a recordset. When I link the MSHflexgrid datacontrol to the recordset the date format is US style (my settings are European). I can use a format statement in the select SQL (e.g. "SELECT format(p.mydate,'dd/mm/yyyy') as mydate from table p") which solves the display problem in the grid.
However when I do that the field is not recognized as a date any longer so any .sort and .filter actions result in numericly sorted rows...

Does anybody have a solution for this?

supunr

Y not write a custom sort for flexgrid.

Private Sub MSFlexGrid1_Compare(ByVal Row1 As Long, ByVal Row2 As Long, Cmp As Integer)
With MSFlexGrid1
Col = 2 ' date column
If (CDate(.TextMatrix(Row1, Col)) < CDate(.TextMatrix(Row2, Col))) Then
Cmp = -1
ElseIf (CDate(.TextMatrix(Row1, Col)) = CDate(.TextMatrix(Row2, Col))) Then
Cmp = 0
Else
Cmp = 1
End If
End With
End Sub

Good Luck!

J_code

ASKER

thanx supunr,
this does solve the sorting problem, but not the filter:
eg. recordset.filter "date_received > #12/03/2003#"

Because of the format() the field in the recorset is not a date (same goes for formatted numbers). So my best solution is if the dates and numbers can be formatted in the MSHflexgrid (not in the recordset).
Is that in any way possible?

GPrentice00

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

-->PAQ - with points refunded

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER

GPrentice00
Cleanup Volunteer

J_code

ASKER

PAQ

ASKER CERTIFIED SOLUTION

YensidMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial