Member Avatar for swatirao

:?: How do I change the input and display of the dtpicker control to "dd/mm/yy" format. Also how do I store it in an MS-Access database in the same format (ie. dd/mm/yy ) . ... need help urgently!

Regards,

Swati

  • 4 Contributors
  • 4 Replies
  • 126 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by Naveed_Cn

Dani AI

Generated

Quick summary and corrections (builds on replies by and ):

The DTPicker is only a display/input control — it does not change how Access stores the value. Keep the field in Access as a Date/Time type and format the display when you show it. was right to warn about CustomFormat overriding the user locale; ’s post has typos (property is CustomFormat, not "CoustomFormate") and the direct string concatenation to build SQL is unsafe and locale‑fragile.

How to wire this safely and predictably:

  • Keep the column type in Access as Date/Time. Don’t store dates as text.
  • If you must format for display only, set the Access form control’s Format property (e.g., dd/mm/yyyy) rather than changing stored data.
  • To save from code, send a real Date value to the database (use DAO/ADO parameters) so the Jet engine never needs to parse a locale-dependent string.

Examples (safe patterns):

DAO recordset (Access/VBA-friendly pattern):

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
rs.AddNew
rs!iDate = DTPicker1.Value
rs.Update
rs.Close

ADO parameter (VB6 / ADO):

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\MyDB.mdb;"
Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = cn
  .CommandText = "INSERT INTO Table1 (iDate) VALUES (?)"
  .CommandType = adCmdText
  .Parameters.Append .CreateParameter("pDate", adDBTimeStamp, adParamInput, , DTPicker1.Value)
  .Execute
End With
cn.Close

Troubleshooting notes:

  • If you ever construct SQL by concatenation (not recommended), you must format the literal in an unambiguous form and use Access date delimiters; however, parameterized commands avoid all that.
  • Using Format(...) in a query returns text; do that only for display columns, not when you need to filter/sort as dates.
  • Prefer a four‑digit year (yyyy) for clarity.

These steps avoid regional/locale surprises and prevent SQL errors and injection risk while giving the dd/mm display you want.

Recommended Answers

All 4 Replies

Member Avatar for davidcairns

For dtPicker you can use the dtPicker.Format to set it to whatever you want, including dtpShortDate and dtpCustom. If you use custom then dtPicker.CustomFormat can be set to any format (including "dd/mm/yy") however this will then ignore the users regional settings. Hence short date is probably the best choice for what you want.

As for storing it in Access, always store it as a date and then when you want to display it do the formatting. You should never store dates as strings in the database. Set the date format either in your SELECT statement or at the point where the data is displayed to the user.

Regards

D

Member Avatar for Ashieni

help me with the dtpicker code

Member Avatar for Ashieni

please help me with the code that dislays the functionality of the dtpicker

Member Avatar for Naveed_Cn

Here you Go...
1:- Firs Place one DTPicker1 on Form.

2:- Go To DTPicker1 Properties and Set Format = 3-dtpCoustom.

3:- Private Sub Form_Load()
DTPicker1.CoustomFormate = "dd-MM-yy"

End Sub
4:- Private Sub Command1_Click()
Dim db As Database
db.Execute ("INSERT InTo Tabel1(iDate) Values(' " & DTPicker1.Value & " ' ")
End Sub

Hope you clear..

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.