-
June 25th, 2012, 10:33 AM
#1
[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.
-
June 25th, 2012, 11:33 AM
#2
Re: Database field format in FlexGrid
Format$()?
Code:
Dim strToday as String
strToday = Format$(Date, "dd/mm/yyyy")
MSHFlexGrid1.Col =2
MSHFlexGrid1.Row =2
MSHFlexGrid1.Text = strToday
-
June 26th, 2012, 06:39 AM
#3
Re: Database field format in FlexGrid
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.
-
June 26th, 2012, 07:22 AM
#4
Re: Database field format in FlexGrid
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...
-
June 26th, 2012, 09:54 AM
#5
Re: Database field format in FlexGrid
Doesn't really work with a MSHFlexGrid
-
June 26th, 2012, 10:08 AM
#6
Re: Database field format in FlexGrid
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
-
June 27th, 2012, 08:55 AM
#7
Re: Database field format in FlexGrid
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?
-
June 29th, 2012, 02:45 PM
#8
Re: Database field format in FlexGrid
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.
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
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.
-
June 30th, 2012, 09:32 AM
#9
Re: Database field format in FlexGrid
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 ?
-
July 2nd, 2012, 03:50 PM
#10
Re: Database field format in FlexGrid
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.
-
July 5th, 2012, 10:21 AM
#11
Re: Database field format in FlexGrid
Can't you just use SQL to access the data, rather than the bound grid?
-
July 11th, 2012, 10:13 AM
#12
Re: Database field format in FlexGrid
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).
-
July 12th, 2012, 11:18 AM
#13
Re: Database field format in FlexGrid
Buffer the RS and edit that one. Then re-write or refresh the bound data.
-
July 13th, 2012, 01:45 PM
#14
Re: Database field format in FlexGrid
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.
-
July 23rd, 2012, 05:17 AM
#15
Re: Database field format in FlexGrid
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|