[RESOLVED] Database field format in FlexGrid

[WoF]

Hello friends,

I'm here for a new question which goes to all database gurus.
I am using an MSHFlexGrid to display records from a table which is part of an MS-Access database.
I do so in creating an ADODB.Recordset which is opened with a querystring to the table, then Flex.DataSource is set to this recordset. This works fine, only one thing does not look good:
There is a field which contains a date. Even if I had defined the format of the field in Access to be dd/MM/yyyy, the column in the flexgrid will use MM/dd/yyyy hh:mm:ss. I just can't figure out how to determine the displayformat for that date column in the flexgrid. I only want to show the date part and not the time, and I want to show it in the local date format, although the system is an english XP.

[dglienna]

Format$()?

   Dim strToday as String
   strToday = Format$(Date, "dd/mm/yyyy")
   MSHFlexGrid1.Col =2
   MSHFlexGrid1.Row =2
   MSHFlexGrid1.Text = strToday

[WoF]

As I reported I do not fill the grid manually. I do

  dim rs as new ADODB.Recordset
  rs.open myQuery$,dbConn
  Set Grid.Datasource = rs

and evrything appears in the grid as expected, except for the date.
In Access I have definitely set the Format for the field to be dd/MM/yyyy, and within Access table display this format is displayed except when entering a cell for edit.

[HanneSThEGreaT]

I may have a stupid solution....

After you have read the data into the FlexGrid, make use of TextMatrix to loop through all your rows, and Format to format the appropriate column, something like :

 Dim FGRow As Long
  
  With Grid1
    .Redraw = False
    For FGRow = .FixedRows To .Rows - 1
      .TextMatrix(FGRow, 1) = Format(.TextMatrix(FGRow, 1), "dd-mm-yyyy")
    Next FGRow
    .Redraw = True
  End With

This is assuming your date column is index number 1.

I hope it helps...

[dglienna]

Doesn't really work with a MSHFlexGrid

[HanneSThEGreaT]

Doesn't really work with a MSHFlexGrid

I wrote this off the cuff as I sadly do not have VB 6 on my system anymore. Have you tested the code? What doesn't work? It would be very helpful to tell us what doesn't work please; not only so that I can try and fix it asap, but so that the OP and other potential readers of this problem also can be helped.
Thanks

[WoF]

I think it should work with the MSHFlexgrid as well. Only the TextMatrix() array delivers strings, so I would have to convert them back to dates before I can Format them?

On the other hand, I didn't want to have a loop. The grid is actuallized on the fly always by Set myGrid.DataSource = rsRecordSet
I don't want to call a loop everytime I do such an update.
Is there no way to specify the format of a column of an MSHFlxGrid like in an Excel sheet?

[HanneSThEGreaT]

I think it should work with the MSHFlexgrid as well. Only the TextMatrix() array delivers strings, so I would have to convert them back to dates before I can Format them?

I don't think so.

On the other hand, I didn't want to have a loop. The grid is actuallized on the fly always by Set myGrid.DataSource = rsRecordSet
I don't want to call a loop everytime I do such an update.

Why not have it in a timer? Or provide an option to the user "Format Date Properly" and by doing so, let them choose when to format the column correctly

Is there no way to specify the format of a column of an MSHFlxGrid like in an Excel sheet?

Unfortunately not.

[George1111]

My Regional Settings are set to Australia (I set the Short Date to Format dd/mm/yyyy)
I use MSHFlexgrid all the time and I always get dd/mm/yyyy in the Grid when setting the Grid.DataSource to the Recordset - exactly as you do it
My Date fields on file are defined as Date/Time fields - as yours are

What are your Regional Date Settings set to ?

[WoF]

Yes, George. But the target computer is in India and has an US-English setting. so according to the OS language it shows m/d/yyyy, although they need dd/mm/yyyy. I shall propose to change the common short date format permanently.

Thank you, Hannes for the final clarity, that there is no other way of doing it as through a loop.
But the idea of doing it only optional seems quite attractive. I think I'll do that, if permanent change of date format is not an option

Sorry for the response delay. Have been sick lately and not able to do computing.

[dglienna]

Can't you just use SQL to access the data, rather than the bound grid?

[WoF]

Well, actually I AM using sql.

  dim rs as New ADODB.Recordset
  rs.open "SELECT * FROM myTable", dbConn, adOpenwhatever, adLockwhatever
  Set flex.DataSource = rs

(Sorry for late reply. Still have been sick).

[dglienna]

Buffer the RS and edit that one. Then re-write or refresh the bound data.

[WoF]

How do you mean, buffer and edit?
How shall I buffer and what should I edit?
And in fact I donÄ't want to rewrite anything to the database here. The grid is only for looking.

[firoz.raj]

Doesn't really work with a MSHFlexGrid

even it will work in MSHFlexGrid

dim rs as new ADODB.Recordset
  rs.open myQuery$,dbConn
  rs.cursorlocation=aduseclient
  Set mshflexgrid.Datasource = rs