Results 1 to 4 of 4
  1. #1
    Lordpanther7 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    2

    Importing for Excel in a format not recognized


    I have a legacy database which imports data from an excel spreadsheet that is generated by another program. Recently my company updated the program to support upgrading to Windows 10. One of the fields in the spreadsheet now lists the date in the format MMM DD, YYYY, TT:TT AM/PM. Since the field contains that second comma Excel doesn't recognize the date and therefore won't allow me to convert it to a different format (MM/DD/YY, TT:TT AM/PM). If I go in and manually delete the comma then Excel will change the format. This would require me to manually change almost 30,000 cells weekly. Access 2013 doesn't recognize the date either due to that comment, and when I go to import the data it leaves the fields empty. What I'm looking for is a way to import the data as a text field into Access (which I know how to do) and then use a query/macro/vba to change the format into a date the program recognizes. I have tried to make a vba program to remove the commas, however, it doesn't recognize the date without a comma. And my experience/knowledge about programing Access is quite limited. To make things worse I work at a job that doesn't allow access to the internet (for help) and the data is contained on a private server. Does anyone know the best/most efficient way to solve my problem?

    Thanks

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Once the data is in Access, is it staying there or do you think it has to go there to "fix" it, then you intend to put it back? The reason for asking is that vba is different for each, and while the function to remove the extraneous comma would be pretty much the same, the rest of it would not be.

    Seems to me that removing the comma (1) would be the way to go, but the exact nature of the data would need to be known. Of most importance would be the consistency of it.
    Last edited by Micron; 04-24-2019 at 03:38 AM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Lordpanther7 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    2
    I plan to keep the data in Access for use. The database calculates various pieces of information based on that date. Since the information is always changing that excel sheet is imported every week. So I'm looking for something I can add to the import process that will allow me to import it as a text string (seems to be the only access will acknowledge its presence) and then convert it to a proper date format (as a date and not a text string). I have a macro that runs that imports the excel information into one table, performs checks to determine if the information has changed then incorporates the new information into a pre-existing table for use by the user.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Common approach is to link sheet as a table then copy to Access table. Usually fixes data type issues but in your case if will not. Suggest you run update query using Replace function to remove the unwanted comma then copy over.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 10-10-2017, 12:06 PM
  2. Importing data from Excel to Access...time format
    By mommyof4kids in forum Access
    Replies: 2
    Last Post: 02-11-2016, 08:40 AM
  3. Combo box is recognized as digit format
    By Gealeks in forum Forms
    Replies: 8
    Last Post: 11-27-2013, 03:08 PM
  4. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  5. Replies: 4
    Last Post: 12-17-2012, 01:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums