Patty-
If you do a straight import, you will most likely get text fields for all the dates. Access does have a date/time data type that would be preferable if you plan to do all your work in Access after you import. By default, a date/time displays in the Short Date format defined for your locale - either mm/dd/yyyy or dd/mm/yyyy. Internally, a date/time data type is a floating point number - the integer part is the number of days since Dec 31, 1899, and the fractional part is the time as a fraction of a day. In addition to the default Short Date format, you can specify just about any other format you want, including spelling out the months and days. (Knowing this, you can creatively subtract one date from another to find out the number of days between the two dates!)
As an example, to convert the sample you provided, you would write an expression like this in a query:
RecDate: CDate(Left(TblDate, 4), & "-" & Mid(TblDate, 2) & "-" & Right(TblDate, 2))
.. where TblDate is the name of the text field containing that date in your imported data. The Left / Mid / Right stuff is simply turning it in to a string that the CDate (Convert To Date) function will recognize: 2015-01-29
After you have that in your query, you can select that expression in the query design grid, open the Properties window, and define whatever display format you want in the Format property of the query field.
Hope that helps...
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jan 29, 2015, at 11:51 PM, pattykf pattykf@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I am pulling raw data from our electronic health record. The program records dates in a variety of formats on different tables. I would like to convert them to a similar format if possible. One of the formats is text with the date in a year month date format. Example 20150129. I import the data so as not to change anything in any health record. I was thinking that I should be able to write a query that pulls info off a table and in that process change the date format. I am really new in access just getting my feet wet as one would say. Any help would be appreciated.
Thank you
Patty
Sent from my Verizon Wireless 4G LTE smartphone
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar