Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format date in Multi-column listbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format date in Multi-column listbox
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) "Sue" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format date in Multi-column listbox
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 .. "Sue" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Form: Can't format date in a Multi-column Listbox | Excel Programming | |||
Filling a Multi-Column Listbox | Excel Programming | |||
Multi Column Listbox Help | Excel Programming | |||
populating a multi-column Listbox | Excel Programming | |||
Date formatting in a multi column listbox | Excel Programming |