Results 1 to 10 of 10
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    formating field

    Hi All,
    I am having a problem copying excel 2007 files into a table in access 2007. The ID field in excel was formatted to number because it was originally a text file that looks like the following: 1.983E+15
    to the following 1979999357230200. When I export these file to access 2007, it appears like this again: 1.983E+15 How do I change it in access to appear as a number. I am using this field as the primary key.
    Please help


    Thanks

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    what's the data type in the access field? the example you gave might be pushing the boundries of what LONGs can hold, but I'm not sure. and it might be reverting to that also because of a maximum length. Might want to check that too.

    Also, are you sure the field in excel is actually a number, and that the file extension is xls instead of csv? You can view csv's just as easy in excel as xls's.

  3. #3
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks for answering me. The file may have originally been a csv file before it got to me as a xls file. I'm not sure. but when I open it; it's in scientific format so I changed it to a number format to get the whole number. In access; the data type is text. i tried changing it to number but it still is in scientific format. This is used as an ID primary field.
    Thanks

  4. #4
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    is that the default display when a column is too narrow?....have you tried just stretching that column wider so it all fits?

  5. #5
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Yes. The column is not too narrow.
    Thanks

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    accoding to this, it can't be helped. Simply out of curiosity, I checked on this in one of my samples. LONGs can only take up to 2.something billion for digit placeholders, but doubles can hold more than 300 places.

    but regardless what I used, the 16th digit resulted in a 0 when using the currency, but result in a "+1" using a double, as it showed scientific notation. and for NTC, my fields have always shown ### signs when a field is not large enough.

    what's even more interesting, is the information here.

  7. #7
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    ouch. not the answer I was looking for. There has to be a way??

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by slimjen View Post
    ouch. not the answer I was looking for. There has to be a way??
    if there is, we don't have it. Hows that for reputable forum help?

  9. #9
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Quote Originally Posted by ajetrumpet View Post
    if there is, we don't have it. Hows that for reputable forum help?
    Whoa! I didn't mean to affend anyone I just thought this is where I can get some help.
    Thanks anyway

  10. #10
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by slimjen View Post
    Whoa! I didn't mean to affend anyone I just thought this is where I can get some help.
    Thanks anyway
    chill out bud. It was a joke.

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

Similar Threads

  1. Formating string
    By Alex Motilal in forum Reports
    Replies: 7
    Last Post: 12-10-2010, 06:09 AM
  2. Conditional Formating In A Report
    By cadsvc in forum Reports
    Replies: 11
    Last Post: 11-02-2010, 04:47 PM
  3. Conditional Formating in pivot table
    By Tina in forum Access
    Replies: 0
    Last Post: 08-25-2010, 10:38 AM
  4. Formating a query
    By lmp101010 in forum Queries
    Replies: 2
    Last Post: 08-17-2010, 05:26 PM
  5. Querry formating
    By Zoroxeus in forum Queries
    Replies: 0
    Last Post: 03-07-2006, 11:00 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