Setting date format for list box

K

Kevin Beckham

I am retrieving data into a list box from an Access database with,
essentially, the following code

:
Dim dbReports As DAO.Recordset
Dim avRows As Variant
:
Set dbReports = gdbEmissions.OpenRecordset(sQuery)

'update list box
With Me.lbxSite
.Clear
If dbReports.RecordCount > 0 Then
avRows = dbReports.GetRows(mlNumRecords)
.List = Application.WorksheetFunction.Transpose(avRows)
End If
.ListIndex = -1
End With
:

Five columns of data are shown, including one column that contains date data.

Everything is fine EXCEPT that the date format is dependent upon how the
display form is run.
If I start with a front-end form that calls the display form, then the date
format is MM/DD/YYYY, contrary to the regional setting
If I start the display form from within the VBA environment then the date
format is DD/MM/YYYY - which is the desired format, and also the regional
setting

Why is there a difference, and can I force Excel to use the regional setting ?

Regards
Kevin Beckham
 
S

Simon Lloyd

You can use this lin
Code
-------------------
Me.lbxSite.Value = Format(Me.lbxSite, "dd/mm/yyyy"
-------------------
I am retrieving data into a list box from an Access database with
essentially, the following cod


Dim dbReports As DAO.Recordse
Dim avRows As Varian

Set dbReports = gdbEmissions.OpenRecordset(sQuery

'update list bo
With Me.lbxSit
.Clea
If dbReports.RecordCount > 0 The
avRows = dbReports.GetRows(mlNumRecords
.List = Application.WorksheetFunction.Transpose(avRows
End I
.ListIndex = -
End Wit


Five columns of data are shown, including one column that contains dat
data

Everything is fine EXCEPT that the date format is dependent upon ho
th
display form is run
If I start with a front-end form that calls the display form, then th
dat
format is MM/DD/YYYY, contrary to the regional settin
If I start the display form from within the VBA environment then th
dat
format is DD/MM/YYYY - which is the desired format, and also th
regiona
settin

Why is there a difference, and can I force Excel to use the regiona
setting

Regard
Kevin Beckha
Click to expand...

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
Top