Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access errors exporting to Excel (Office 2000 Sr1a)

    I have just noted that a query I have created in Access fails to correctly export its data to and Excel spreadsheet. The query returns six fields - date, sender, francs, euros, dollars and receiver- which appear to be ok on viewing the query result.
    But when I choose the menu item "Export query to xls" from the context menu, the currency data for one sender is all exported to the same column (francs). That is, for the range of rows for this sender all three currency columns are lumped together into one column - an obvious mistake. I repeat that when you view the data in the access query result this does not happen.
    Seems like a bug. Anyone know anything about this?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    The way your post is phrased, it sounds like you have multiple calculated columns for several senders. Yet you say this is problem occurring only in one of these?
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    Yes there are three calculated columns.
    Actually I have found that although it affected all the results of one sender, it is not confined to that sender.
    It occurs with apparent randomness throughout the spreadsheet generated. It is really weird. I will post some sample files if I can reproduce the behaviour with them. I admit the query is rather complex (I am trying to think of a simpler one that achieves the same results) and maybe the truncation limit for the SQL comes at an earlier point when you export than it does if you just run the query. But then it would work at all then, would it? Very weird.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    I really cant' figure out what is wrong - I don't think it is me!
    So, here is a cut down version of the database I'm working on which should enable the Admin user to log on without a password.
    Check the query TRAN and
    compare it with the exported version TRAN.XLS

    They are different!

    Maybe it might be worth doing an export on another system to see if it still happens.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    When I run your database I get all the values in the last column (AUD). This is the same however I do it. run the query, show it as a datasheet, or export it to excel.

    I agree this is different from your Trans.xls. How did you produce that one from the database posted?

    I note that the formulas you use are VERY long. I do not know if that could be a problem.
    David Grugeon
    Brisbane Australia

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    Are you running Windows 2000 Professional as your operating system?
    I just downloaded the file I posted and checked and I STILL get my results - ie the query puts all the values in the last column in the database but when exported to Excel some appear in the Francs (FRF) column.
    I am simply right clicking on TRAN in the query window
    of the opened database and choosing to export to Excel in Office97-2000 FORMAT.
    Sheeeeesh, now it depends on the computer so it is buggy in its bugginess!!

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    Curiouser and curiouser!!
    I just tried creating a table from the TRAN query first and it wouldn't allow me as it said I couldn't have more than one Autonumber field. But I didn't try to.
    Then I appended the data to a specially constructed table by converting TRAN into an APPEND query.
    That worked and all the data was in the correct columns.
    But when I exported this CORRECT TABLE to Excel - the same result occurred as before!! ie data in the wrong columns.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    Hi David,
    I am running Win2k and get the same results as you. When I look at the query in design view I'm actually amazed it runs at all since the expressions you have in there appear to end in mid sentence! (e.g. FRF appears to end with [Transact followed by nothing.)
    Have you tried aliasing your tables (e.g. Alias Transactions as T) to make the expression shorter?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    It sounds like it might be an Excel problem more than an Access problem. Try exporting the file to Word merge format, which is tab delimited. Then open that file in Excel and see what you get.
    Charlotte

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    Yes Charlotte that works. Moreover I tried exporting to Excel 3.0 AND Excel 4.0 and they work too.
    (unless other bugs are lurking unseen)
    but Excel 5-7 and 97-2000 don't work.
    So new "improved" versions of Excel can't be trusted for exporting to from anything that isn't dead simple in Access. This then makes you wonder what other errors occur in the export process and then how much can you depend on Office software at all when dealing with matters where accuracy is essential.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    I tried exporting to Excel 3 and 4 and they worked fine.
    I tried exporting to .csv format and it wouldn't work at all.
    It works exporting to Word .txt format though.
    It's hard to know where the error is occuring, I guess it must originate in access but only show up under certain circumstances when exporting to another file format.
    I would dearly love to know a technique for shortening all my lengthy SQL statements - some technique to reduce their complexity as well as the statement length while achieving the same result.
    Any ideas please?

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    Curious!
    For reducing the length of the SQL, you can alias tables (right-click on one in the query grid, choose properties and then enter the Alias) so, for example, if you alias Transactions as T, you can then refer to T.fieldname rather than Transactions.Fieldname which can be very effective if you have lengthy tablenames. (When I link to our Oracle database, I end up with table names like USERNAME_COMMON_POLICY_DETAILS_01 so I use it a lot!). It also allows you to use the same table multiple times in a query, which can be useful.
    As for reducing complexity, I can't say specifically as I can't see the entire formula that you're using!
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    I have the answer: I hope this experience will help others.
    My query returns nulls when it should be returning zeros. By using the NZ function you get the same result in access when you look at the query generated but it is not the same really.
    When you export the "repaired" query it comes out right in all fomats, including Excel 97-2000.
    (Except .csv where you get an immediate error message saying "too few parameters, expected 1"
    whatever that means.
    So beware the dreaded NULL is the moral.

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    I am on NT4 operating system.

    Tried your way of exporting it and still get it all in the AUD. Looks like it might be an op-system issue but its an odd one.
    David Grugeon
    Brisbane Australia

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access errors exporting to Excel (Office 2000 Sr1a)

    Actually I have sort of solved the problem - problems exporting Nulls to some versions of Excel.
    But why this should depend on the operating system employed is a total mystery to me.

Page 1 of 2 12 LastLast

Posting Permissions

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