Results 1 to 5 of 5
  1. #1
    bhorwath is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Oct 2021
    Posts
    8

    Valid Access Query does not export to Excel

    Here is the setup, a colleague has a mundane task of monitoring work permit status. Every month, he posts a list of personnel and relevant permits upcoming for renewal. He typically uses an access database and just organizes the expiry date for various permits to compile the list of those due for renewal. I'm more familiar with Access and setup the query to narrow results for relevant personnel, based on upcoming renewals for the end of the current month, grouped by renewal due in 30, 60, 90, or 120 days. I then duplicated that query for various other permits which are posted monthly. The queries work flawlessly in Access, it is beautiful to see hours of work every month resolved with a click of a button moving forward.



    However, when exporting the queries to Excel to have it formatted in a more presentable manner when posted; one exported query imports properly, and another only imports the header information and a few blank cells below.

    The only difference between the two queries are relevant permit numbers (arbitrary informational relevance that should not impact the import to Excel).

    Why would two similar queries based on the same Access table, using almost identical data sets and calculation formulas, not import in the same manner?

    Any help is greatly appreciated. Apologies if this is in the wrong forum for this post.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No way to know without knowledge of data and the query structures and method of export. If you want to provide db for analysis, follow instructions at bottom of my post.

    What formatting do you need that must use Excel and not Access report?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You keep mentioning "import", have you tried to initiate the transfer from Access by exporting the queries using Docmd.TransferSpreadsheet? If the queries use dynamic parameters (from a form) the import from Excel might not work as expected.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    I have found that if the Access Function NZ is used in the query, it will not import/transfer to Excel. Do you have any NZ functions in your query?

  5. #5
    bhorwath is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Oct 2021
    Posts
    8
    Thank you to all that replied. I was able to get this to work properly by deleting a field in the query that was not importing, then adding the field back into the query and moving the data field back to the original position when setting up the parameters of the query. The end result of the action should've changed nothing, but somehow apparently it did something and the query imports perfectly now.

    Couple things to add that might have been relevant, it is a password protected database that I had to move over to legacy encryption to even import to Excel at all. Both queries are imported to separate worksheets in the same excel file.

    Again, thank you to all the replies. Happy Holidays.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-19-2021, 02:38 AM
  2. Export query to excel with Access 2007 VBA
    By jdashm in forum Access
    Replies: 11
    Last Post: 04-17-2017, 02:28 PM
  3. VBA, Integrating Excel and Access query/export
    By kestefon in forum Programming
    Replies: 7
    Last Post: 12-12-2013, 07:28 PM
  4. Replies: 7
    Last Post: 04-25-2013, 03:47 PM
  5. Access Query export to Excel form
    By Odogggoneit in forum Access
    Replies: 1
    Last Post: 01-14-2013, 05:16 PM

Tags for this Thread

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