[RESOLVED] Database field format in FlexGrid

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.

Format$()?

Code:

---

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

---

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

Code:

---

  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.

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 :

Code:

---

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

Doesn't really work with a MSHFlexGrid

Quote:

---

Originally Posted by dglienna

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

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?

Quote:

---

Originally Posted by 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?

---

I don't think so.

Quote:

---

Originally Posted by WoF

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

Quote:

---

Originally Posted by WoF

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

---

Unfortunately not.

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 ?

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. :)

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

Well, actually I AM using sql.

Code:

---

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

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

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.

Quote:

---

Doesn't really work with a MSHFlexGrid

---

even it will work in MSHFlexGrid

Code:

---

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

---

Is using the Flexgrid a requirement? As far as I know there is no way to auto format the data in a flex grid but this can be done easily in a DBGrid. Would you be able to use that instead?

He wants to CHANGE THE FONT.

Quote:

---

Originally Posted by dglienna

He wants to CHANGE THE FONT.

---

Where did you see this? I see no reference to changing font anywhere in the thread except your post. I see that he wants to change the format which is simple with the dbgrid, you can afaik also change the font for the dbgrid as a whole.

Have you tried to write all the fields of the record instead of use the *
Example
SELECT Fieldname1, Fieldname2, Format(Fieldname3,"dd/mm/yyyy" ), Fieldname4 FROM ...

Edited to say FORMAT rather than FONT. Don't know what CG did to the edit...

Sorry for late replying, still suffer from sickness.

@jggtz: I have selected the fields to go to the list all by name. No "*".
I will try this:
SELECT Fieldname1, Fieldname2, Format(Fieldname3,"dd/mm/yyyy" ), Fieldname4 FROM ...
although I never saw something like a format statement embedded in an sql query. Thanks.

@DataMiser and David: I used MSHFlexGrid, because I was already familiar to it. I might try DataGrid if you say it can change the format for a column, but I have one thing in common to all lists in the program: I have made sorting occur when clicking on the column headers. Should be able to handle this in a similar way with DataGrid. I will take a look to that control, thanks.

Normal Flexgrid lets you intercept click events. Look for the FLOATING TEXTBOX examples to replace actual cells with input.

Right syntax

Code:

---

SELECT Fieldname1, Fieldname2, Format(Fieldname3,'dd/mm/yyyy' ) As Exprname3, Fieldname4
FROM    Tablename1

---

https://www.google.com.mx/#hl=es-419...iw=800&bih=457

Why post TWICE?

Seems because there is a link now to the source of the information.

I don't need to change data while in grid view. It is only for display.

Ok, fine.
jggtz, your solution works perfect. I have to change the final query string just before I open the recordset, though, but this is a minor change in the end.

Very good. Thank you.

good work jggtz :thumb:

Unbelievable