Community discussion forum

dtPicker date format query

• • Reply

  1 year ago

  by bronx

  bronx

  , South Africa

  Joined 7 years ago
  Hi I am using this to draw data from a access database. strSqlStatement = "SELECT * FROM BankRecon WHERE DateDiff('d',0,Date)>= '" & DateDiff("d", 0, Format(DTFrom.Value, "dd/MM/yyyy")) & "' and DateDiff('d',0,Date)<= '" & DateDiff("d", 0, Format(DTTo.Value, "dd/MM/yyyy")) & "'" Is there anyway that I can change the code so that it will convert the date format in the table to "dd/MM/yyyy" if it is not in that format before I extract the data? The data could be in the mm/dd/yyyy format and I want it in the dd/MM/yyyy format. Thanks
  Report abuse
• • Reply

  1 year ago

  by konikula

  Matej Kasper

  Trutnov, Czech Republic

  Joined 3 years ago
  MS Access stores date/dateTime as date/dateTime TYPE, what implies, that it is not stored as FORMATTED STRING. So if you are needing to print retrieved date / time in specified format, u must use myDate.ToString("MM/dd/yyyy"). You cannot change the form in which data are accepted by insert / update commands, but anyway you can encapsulate FormatDate to some function I have for example
  Shared Function USAccessDT$(ByVal d As Date)
  If lCondition.IsFullDate(d) Then
  Return "#" & d.ToString("yyyy\/MM\/dd H:m:s") & "#"
  Else
  Return "NULL "
  End If
  End Function
  Report abuse