Office 2010/Access 2010

Advertisement


Question
I have a table being pulled from a obdc database. This table contains the date and time in the below formats

Date: 20130620
time: 74658

I need a formula or something that can assist me change these formats to the following format:

Date: 6/20/2013
Time: 7:46:58


In the Access 2007, I was using the following formulas on my query but I recently upgraded to 2010 and these formulas don't work, Below are the formulas I was using in the 2007 version.

Time: Where babifile_ftmhcmp.ctime is my table name and field name:
TIME: IIf(Len([BABIFILE_FTMHCMP].[CTIME])=6,Left$([BABIFILE_FTMHCMP].[CTIME],2) & ":" & Mid$([BABIFILE_FTMHCMP].[CTIME],3,2) & ":" & Right$([BABIFILE_FTMHCMP].[CTIME],2),IIf(Len([BABIFILE_FTMHCMP].[CTIME])=5,Left$([BABIFILE_FTMHCMP].[CTIME],1) & ":" & Mid$([BABIFILE_FTMHCMP].[CTIME],2,2) & ":" & Right$([BABIFILE_FTMHCMP].[CTIME],2),"00:" & Left$([BABIFILE_FTMHCMP].[CTIME],2) & ":" & Right$([BABIFILE_FTMHCMP].[CTIME],2)))

Date:
DATE: CDate(Mid([BABIFILE_FTMHCMP].[CDATE],5,2)+"/"+Right([BABIFILE_FTMHCMP].[CDATE],2)+"/"+Left([BABIFILE_FTMHCMP].[CDATE],4))

Answer
Hi Jessica:

Thank you for the question.

I am not an expert on Access; however, the following may assist you in what you are attempting to do.

= Format(Now(), "hh:mm AMPM")    
If the current time is 5:04:23 in the afternoon, the expression returns 05:04 PM.
= Format(Now(), "Long Time")    
Returns current time in the system-defined long time format.
= Format(Now(), "h:m:s")    
If the current time is 8:04:23, the expression returns 8:4:23.
= Format(Date(), "dddd, mmm d yyyy")    
If the current date is 11/14/2003, the expression returns "Friday, Nov 14 2003".

You can use these expressions in a calculated field in a query.
Expression    Description
TimeStamp: Format(Now(), "h:m:s")    
In the TimeStamp field, displays the time as 10:50:7, if the current time is 10:50:07.
DayAndDate= Format(Date(), "dddd, mmm d yyyy")    
Sets the DayAndDate field to "Tuesday, Dec 2 2003" if the current date is 12/02/2003.

Hope this helps.

Regards,


Mary Lindsey

Office 2010

All Answers


Answers by Expert:


Ask Experts

Volunteer


Mary A. Lindsey

Expertise

New features of Office 2010

Experience

Microsoft Certified trainer with over 20 years of experience

Organizations
ASTD, ASTE

Education/Credentials
Microsoft Office 2010 Certified instructor

©2016 About.com. All rights reserved.