Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Location
    UK
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    missing fields in export (2000)

    I have a simple spreadsheet with values for min,sec,hun in three fields. Not all the records have values in all three fields. When I export the table/query to excel2000 some of the numbers turn up in the wrong columns. It appears that where a record does not have a value in say the min field, excel shifts the sec and hun along into the min and sec field. So making the export absolutely usless. I have no idea why or how to stop it. I have tried several times. Any help?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: missing fields in export (2000)

    Hmmmm - this sort of thing usually works so something must be amiss. How are you exporting the table/query to Excel - using the export wizard? Also I am curious why you appear to be splitting time into minutes, seconds and hundreths of seconds? The internal date format in Access is capable of storing this data as a single field - does your data come from some external source? Finally, have you considered making your min field zero rather than allowing it to be null (blank) if there isn't any data?
    Wendell

  3. #3
    New Lounger
    Join Date
    Mar 2001
    Location
    UK
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: missing fields in export (2000)

    Thanks for the advice. The data is coming in from excel to eventually go back out combined with other data back into excel. I also am not sure how to format an access field to show seconds,min,hundreths. Any advice?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: missing fields in export (2000)

    Is this actually a time coming in from Excel in one field or is it spread across columns? If it comes in as a time value from Excel, then you can simply specify that the field it is imported to is a date/time field. Then you can specify its format as "hh:mm:ss" - I'm not sure how the formatting for hundreths of a second is handled at the moment. In any event, date/time fields occupy 8 bytes in a floating point format where the part to the left of the decimal is the number of days since 8/30/1899, and the part to the right is the portion of the day that represents time. Thus there is sufficient accuracy to analyze down to about 10msec.

    My suspicion is that you are getting tangled up in null and zero-length string problems. You can check for that by seeing if you have entries in the table that are null, and then checking for records that have "". Not sure why either would cause problems with an export to Excel format. Or are you taking it to a text (.csv or .txt) format?
    Wendell

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: missing fields in export (2000)

    ... Typically when you export to excel and your columns of data shift to the left indicates that you have null values in your data tables. This agrees with your statement that not all the records have values in all three fields. To correct this, use the NZ function in access to convert all of your null values to zeroes. The data will then export in the correct columns.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •