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
+ 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. Thanks! Nov 2 '12 #1
Share this Question
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. -Frinny 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 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. 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 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: Expand|Select|Wrap|Line Numbers YourRealDate: DateSerial (Left([YourDate], 4), Mid([YourDate], 5, 2), Right([YourDate], 2)) -Stewart 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.