Format date in excel userform listbox 1

[JonoB]

Hi,

I have a multi-column listbox in an excel userform that is populated from a recordset (using .getrows). The recordset is from an Access database.

One of the columns in the listbox is a date field and although the date format in the Access database is DD/MM/YY (and I have verified that all the dates are in fact stored in this manner), the date is shown as MM/DD/YY in the Excel listbox. i.e. the DD and MM are swopped around.

I have also made sure that the date format in local and regional settings for my pc is set to DD/MM/YY.

I guess that Excel's listbox automatically shows dates in MM/DD/YY format

Any ideas on how to make sure that the date format in the Excel listbox is shown as DD/MM/YY ??

Thanks for any help!

 

[SkipVought]

Hi,

date format in the Access database is DD/MM/YY

The FORMAT is for entry and display purposes. The DATE is stored as a NUMBER like today is 38215 (day count starts with 1/1/1900)

So when your date needs to be DISPLAYED, you'll need to FORMAT when you load the listbox
[tt]
Format(MyDate, "mm/dd'yy")
[/tt]


Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[JonoB]

[Highlight]So when your date needs to be DISPLAYED, you'll need to FORMAT when you load the listbox

Format(MyDate, "mm/dd'yy")[/Highlight]

I had thought of that, but as I previously said, the listbox is populated using the .getrows method as shown in the following code


'Populate the combo if there are records
If rsOpenSiteID.BOF And rsOpenSiteID.BOF Then
Me.lstModels.Clear
Me.txtDescription.Text = "No models for this site"
Else
Me.lstModels.Column = rsOpenSiteID.GetRows
Me.txtDescription.Text = ""
End If