Format date in Multi-column listbox

[Sue]

Hi there

For some reason data that's fed into a multi-column listbox on an
Excel Form via an SQL query from Access, won't maintain it's
shortdate
format of dd/mm/yyyy (we're in Aus, so our preferred format), even
though this is the specified format within the design of the Access
table, and the code that sits behind the text boxes on the form (into
which new data is entered in the first instance - code below)..


Even weirder is that every now and then when I reopen that record in
the Excel form, it does switch over to [at least] d/mm/yyyy??


Is it possible to format a column with a multi-column listbox??


Any help would be really appreciated..

Cheers in advance
Sue


____________________________________________________


Private Sub cmbok_click()


On Error GoTo ErrMsg:


Set ws = DBEngine.Workspaces(0)
Call FindDatabasePath
Set db = ws.OpenDatabase(path1)
Dim rsA2 As Recordset
Const DateFormat = "dd/mm/yyyy"


If Me.txResources = "" Or IsNull(Me.txResources) Then
MsgBox "Please type the name of the Resource in the textbox
before
clicking this button, or select an existing name from the list of
Available Resources and click 'Add'.", vbOKOnly, "Add New Employee"
Exit Sub


End If


Set rsA2 = db.OpenRecordset("SELECT * From tblResource")
With rsA2
.AddNew
.Fields("ResourceName") = Me.txResources.Value
.Fields("BusinessUnit") = Me.txBusinessUnit.Value
.Fields("LineManager") = Me.txLineMgr.Value
.Fields("StartDate") = Format(Me.txStart.Value, DateFormat)
.Fields("FinishDate") = Format(Me.txEnd.Value, DateFormat)
.Fields("% Effort") = Me.txEffort.Value
.Fields("CostCentre") = Me.txCostCentre.Value
.Update
End With


Call RequeryResourcesLists


Me.txResources.Value = ""
Me.txBusinessUnit.Value = ""
Me.txLineMgr.Value = ""
Me.txStart.Value = ""
Me.txEnd.Value = ""
Me.txEffort.Value = ""
Me.txCostCentre.Value = ""


Me.lstboxAvailableResources.SetFocus


rsA2.Close
Exit Sub


ErrMsg:
MsgBox "You must complete all fields before clicking ok", vbOKOnly,
"Add New Resources"


Exit Sub


End Sub

 

[Joel]

You may have a string in Access rather than a true date. Try this change.

from
.Fields("StartDate") = Format(Me.txStart.Value, DateFormat)
.Fields("FinishDate") = Format(Me.txEnd.Value, DateFormat)
to
.Fields("StartDate") = Format(datevalue(Me.txStart.Value), DateFormat)
.Fields("FinishDate") = Format(datevalue(Me.txEnd.Value), DateFormat)

 

[Karthi Ganesh]

Hi

Cant you change the SQL query itself, such that it returns a single column
but formated as mutliple column? In many occasion it worked for me .

something like this:

select left(name,20)&"|"&format(dob,...) from ..