By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,466 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Converting dates in text format to dates

P: 1
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.

Nov 2 '12 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 5K+
P: 9,731
Consider using the Format function to format the string number into an acceptable date string that you can pass to the cdate function.

Nov 2 '12 #2

Expert Mod 100+
P: 2,321
There is probably alot smarter ways to do this but you could try:

Expand|Select|Wrap|Line Numbers
  1. Public Function ConvertDate(strDate As String) As Date
  2.   Dim intYear As Integer
  3.   Dim intMonth As Integer
  4.   Dim intDay As Integer
  5.   intYear = Left(strDate, 4)
  6.   intMonth = Mid(strDate, 5, 2)
  7.   intDay = Right(strDate, 2)
  8.   Dim s As String
  9.   s = "" & intMonth & "/" & intDay & "/" & intYear
  10.   ConvertDate = CDate(s)
  12. End Function
This video will show you how to implement custom functions in access.
Nov 2 '12 #3

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   [YourDate], 
  3.    DateSerial (Left([YourDate], 4), Mid([YourDate], 5, 2), Right([YourDate], 2)) AS YourRealDate
  4. FROM
  5.   [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:

Expand|Select|Wrap|Line Numbers
  1. YourRealDate: DateSerial (Left([YourDate], 4), Mid([YourDate], 5, 2), Right([YourDate], 2))
Nov 2 '12 #4

Expert Mod 100+
P: 2,321
I had forgotten about the DateSerial function. Thanks for the reminder.
Nov 2 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.