Converting dates in text format to dates

[BrendaR]

Hello. I need to convert dates in this format to recognizable dates:

20121102 (formatted in text).

In Excel, the conversion formula is =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

I can not figure out how to convert them in Access.

Thanks!

[Frinavale]

Consider using the Format function to format the string number into an acceptable date string that you can pass to the cdate function.


-Frinny

[TheSmileyCoder]

There is probably alot smarter ways to do this but you could try:

Public Function ConvertDate(strDate As String) As Date
  Dim intYear As Integer
  Dim intMonth As Integer
  Dim intDay As Integer
  intYear = Left(strDate, 4)
  intMonth = Mid(strDate, 5, 2)
  intDay = Right(strDate, 2)
  Dim s As String
  s = "" & intMonth & "/" & intDay & "/" & intYear
  ConvertDate = CDate(s)
 
End Function

This video will show you how to implement custom functions in access.

[Stewart Ross]

If you want to do the conversion in an Access query you can use a computed field based on the DateSerial function in Access that is very similar to the Date function in Excel which you quote.

The SQL would be something like this:

SELECT 
  [YourDate], 
   DateSerial (Left([YourDate], 4), Mid([YourDate], 5, 2), Right([YourDate], 2)) AS YourRealDate
FROM
  [YourTable];

The names of the actual fields and the table or query concerned would have to be used in place of the examples above, but it might get you started without having to define a custom function first.

If you use the Access query editor you can enter the following in the Field Name part of the grid to create the computed field:

YourRealDate: DateSerial (Left([YourDate], 4), Mid([YourDate], 5, 2), Right([YourDate], 2))

-Stewart

[TheSmileyCoder]

I had forgotten about the DateSerial function. Thanks for the reminder.