Full Version: Need to convert date imported from Excel into an Access text fie
UtterAccess Forums > Microsoft® Access > Access Date + Time
marie
I am working with an existing form created in Excel. I have to import this form into Access. Because the form is laid out like a paper form you'd fill out by hand (the data isn't laid out like a table), all the fields come in as text and are loaded 'raw' into a new table. Then, I have to parse the data in the newly imported table using a Macro so I can load the fields into a database table. (i.e., field by field I have to look for data in one column, such as a literal 'Name', and then look for the actual Name field data in another column of the same record, and then load that into the real table in Access.)
I have several date fields in the Excel form that are formatted as a date, usually Short Date format (mm/dd/yy). However, when the form is imported into Access, the date comes through as a serial number date, and Access doesn't recognize it as a date. Is there a function someone may know of that will convert the serial number back into a date?
Interesting side note -- most of the Excel forms have the date formatted to display in Excel as 'mm/dd/yy', but some of the forms have the date formatted to display in Excel as 'month dd, yyyy'. On the few forms where the date is formatted to display in Excel as 'month dd, yyyy', the date comes into Access correctly and is recognized as a date when I try to load it into the real table. It would be really nice if I could have all the forms formatted this way, but I am dealing with pre-existing historical data primarily during this 'bulk load' phase.
Thanks for any help you can provide!
Larry Larsen
Hi Marie
Welcome to Utter Access Forums
Could you give us an example of your serial number date..?
Also you talk about form import..? (is this data within the form or the actual form)
thumbup.gif
marie
Larry,
For example, the date 3/1/2005 comes into Access as 38412, and the date 3/11/2005 comes into Access as 38422. I assumed this was the '1/1/1900 plus nn days' computation for a date.
The form is a paper form that was designed in Excel. I have to import the whole form into Access as a new table. Then, by parsing through the Excel form data and looking for specific 'field names', I copy some of that data into another table in Access as a new record.
Hope this helps. Thanks!
Marie
Larry Larsen
Hi
If I run those references through the Format() function it convert's is this useful..?
eg:
? Format("38412","dd/mm/yyyy")
Result > 01/03/2005
? Format("38422","dd/mm/yyyy")
Result > 11/03/2005
(Note: I use UK date format)
thumbup.gif
marie
That works BEAUTIFULLY!!! Thanks so much! I didn't realize that the Format function was so flexible.
Larry Larsen
Hi Marie
You're welcome..
thumbup.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.