Member Avatar for Krs13

Hi,

I have an access database where in i have 3 separate fields for day, month and year which are comboboxes. I have an separate field where i need to combine these 3 fields into that single field(dd/mm/yyyy). can somebody help me on this please.

Edited by Krs13
  • 3 Contributors
  • 3 Replies
  • 129 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by rishif2

Dani AI

Generated

Three practical options, plus a few pitfalls to watch for.

and both showed string concatenation; that works for display but stores a string. Prefer storing a real Date/Time value (so sorting, date math and regional handling work correctly) and either build that Date in VBA with DateSerial or use a calculated control / query expression so no code is needed.

Example VBA pattern (place in the form module and call from each combobox AfterUpdate). This produces a Date value, not a plain text string:

' In the form module
Private Sub UpdateCombinedDate()
    If Nz(Me.cboDay, "") = "" Or Nz(Me.cboMonth, "") = "" Or Nz(Me.cboYear, "") = "" Then
        Me.txtCombinedDate = Null
        Exit Sub
    End If

    Dim d As Date
    d = DateSerial(CInt(Me.cboYear), CInt(Me.cboMonth), CInt(Me.cboDay))
    Me.txtCombinedDate = d    ' bind txtCombinedDate to a Date/Time field
End Sub

Private Sub cboDay_AfterUpdate()
    UpdateCombinedDate
End Sub

If no VBA is wanted, put a calculated textbox on the form or in a query: set its ControlSource to =DateSerial([YearField],[MonthField],[DayField]) and give it a Format property of dd\/mm\/yyyy for display. That keeps the form reactive without manual event code.

Troubleshooting and cautions: use AfterUpdate (not OnChange) so the final selection is available; ensure combobox BoundColumn returns numeric values or use CInt; handle Nulls (Nz/IsNull) before calling DateSerial; save the record with Me.Dirty = False if persistence is required. To control appearance use the Format function or the control’s Format property. See Microsoft Docs for the DateSerial function and the Format function for exact behavior and formatting options: DateSerial function and .

Recommended Answers

All 3 Replies

Member Avatar for AndreRet

Try the following -

Dim xYear As Integer, xMonth As Integer, xDay As Integer, xResult as String

xYear = rs!year ''Change rs to your record selector name...
xMonth = rs!month ''Also change the field names year, month, day to your field names...
xDay = rs!day

xResult = xDay & "/" & xMonth & "/" & xYear

MsgBox xResult
Edited by AndreRet
Member Avatar for Krs13

Can you please guide me as to where this code should appear. Something like in on update field or on change... thanks

Member Avatar for rishif2

try the following:-

Dim dt1 As String
dt1 = Combo1.Text & "/" & Combo2.Text & "/" & Combo3.Text
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open App.Path & "\db.mdb"
con.Execute  "INSERT INTO tbl(dtfield) VALUES('" & dt1 & "')", adCmdText
con.Close
Set con = Nothing

if still not answered , then please make your thread more clear else mark this post as solved . . .

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.