locked
Run time error 3070 when exporting crosstab to Excel RRS feed

  • Question

  • My client is getting a runtime error 3070, Access doesn't recognize the field name, when exporting a query based on a crosstab query to Excel.  The query works fine when displaying instead of exporting on my client's system and works fine both displaying and exporting on my 2 system.  I found one post related to this but it didn't have a specific solution for the problem: http://www.utteraccess.com/forum/lofiversion/index.php/t1853425.html

    Any ideas on what might be going on?

    Using Office/Access/Excel 2007.  Latest updates on my systems, not sure of update status on client system.

    The field that originally had the error was shown as [rqryClientIntakeSummaryWithProtocolStepStatus].[Cross-c].  This field, currently called rqryIntakeProtocolStepStatus_Crosstab.[Crosscheck GPRA questions 1 and 1a] was originally rqryIntakeProtocolStepStatus_Crosstab.[Cross-check GPRA questions 1 & 1a]. I changed it to remove the the dash and ampersand but that did not correct the problem.  I don't have the current field name that is in error but will post it once I do.

    -- Here is the query that fails (rqryClientIntakeSummaryWithProtocolStepStatus):

    SELECT DISTINCT rqryClientIntakeSummary.ClientID AS ID, rqryClientIntakeSummary.FirstName, rqryClientIntakeSummary.LastName, [StaffFirstName] & " " & [StaffLastName] AS Staff, rqryClientIntakeSummary.ProgramID, rqryClientIntakeSummary.IntakeCompletionDate, rqryIntakeProtocolStepStatus_Crosstab.[Consent Form], rqryIntakeProtocolStepStatus_Crosstab.[Locator Form], rqryIntakeProtocolStepStatus_Crosstab.DH, rqryIntakeProtocolStepStatus_Crosstab.[Gain-Q], rqryIntakeProtocolStepStatus_Crosstab.[Crosscheck GPRA questions 1 and 1a], rqryIntakeProtocolStepStatus_Crosstab.SSM, rqryIntakeProtocolStepStatus_Crosstab.[UA Results], rqryClientIntakeSummary.FollowUpNotes, [CounselorFirstName] & " " & [CounselorLastName] AS Counselor, rqryClientIntakeSummary.DischargeStatus, rqryClientIntakeSummary.[6MonthFUStatus]

    FROM rqryClientIntakeSummary
    INNER JOIN rqryIntakeProtocolStepStatus_Crosstab
    ON rqryClientIntakeSummary.ClientKey = rqryIntakeProtocolStepStatus_Crosstab.ClientFK

    ORDER BY rqryClientIntakeSummary.ClientID;

     

    -- Here is the crosstab query that it depends on (rqryIntakeProtocolStepStatus_Crosstab):

    TRANSFORM First(rqryIntakeProtocolStepStatus.TimePointProtocolStatusID) AS FirstOfTimePointProtocolStatusID

    SELECT rqryIntakeProtocolStepStatus.ClientFK, rqryIntakeProtocolStepStatus.TimePointTypeID

    FROM rqryIntakeProtocolStepStatus

    GROUP BY rqryIntakeProtocolStepStatus.ClientFK, rqryIntakeProtocolStepStatus.TimePointTypeID

    PIVOT rqryIntakeProtocolStepStatus.TimePointProtocolStepID;

     

    P.S. This will be bit difficult and time consuming to debug since I can't recreate it here and need to rely on my client, who works part time, to install changes and test it on her system.

    Thanks


    Carl
    Wednesday, May 4, 2011 2:09 PM

Answers

  • Hi Bruce,

    Thanks for checking in.  I'm still waiting for my client to get the installation done.  Their IT guy has been away the last couple of weeks.  I'll touch base with them again to check on the status.

    The work around they are using for the time being is to run the query to screen and then copy/paste the results into Excel.


    Carl
    • Marked as answer by Bruce Song Monday, May 23, 2011 9:23 AM
    Wednesday, May 18, 2011 1:11 PM
  • Resolved:

    Installing the full Office 2007 suite, and hence Excel 2007 corrected the problem and the client reports that it is working fine.


    Carl
    Wednesday, May 25, 2011 2:14 PM

All replies

  • Hi Carl,

    It is difficult for me to reproduce your problem on my side.

    Please take a look at this thread about the similiar problem: http://www.pcreview.co.uk/forums/crosstab-query-access-97-a-t1177702.html which says it need to declar the Paramether type. Does the suggestion help you?

    If this does not help you. It will be better if you can share your demo file with us on the skydrive and share the link with us so that we can reproduce your problem and give you the solution.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, May 6, 2011 6:46 AM
  • Hi Bruce,

    Thanks for your reply.  To address you suggestions:

    - There are no parameters in this series of queries, so that is not the issue.

    - I can't reproduce the problem either.  I've tried it on both of my systems and it works, it only seems happen on my client's system.

    But, I have some additional information:

    - It is failing on the field "rqryIntakeProtocolStepStatus_Crosstab.[Crossche]" which I'm assuming is a truncated version of the full field name rqryIntakeProtocolStepStatus_Crosstab.[Crosscheck GPRA questions 1 and 1a].

    - More importantly I discovered that my client is running Access 2007 but the rest of her office suite is at 2003.  She also does not have Access/Office 2007 SP2 installed.

    At this point I'm going to hold off any further work on debugging this until she has a full Office 2007 with SP2 installed.  I'll post back at that point as to whether or not that fixed the problem.

    Thanks again.


    Carl
    Friday, May 6, 2011 11:25 AM
  • Hi Carl,

    If that still did not fix the problem, just feel free to post back. We will try our best to help with you about this problem.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, May 9, 2011 9:07 AM
  • Hi Carl,

    What's the status of the problem on your client's system? Does it still happen? Just feel free to let us know the progress.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 12, 2011 2:52 AM
  • Hi Bruce,

    Thanks for following up.  They are still getting the 3070 error.  We're waiting on their IT guy to install the full Office 2007 suite to see if that fixes the problem.  I've sent them a message asking for the timeline.


    Carl
    Friday, May 13, 2011 12:34 PM
  • Hi Carl,

    How about installing the full Office 2007 suite? Does the action help you to resolve the problem? If not, just let me know. I will do my best to help with you.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 18, 2011 10:52 AM
  • Hi Bruce,

    Thanks for checking in.  I'm still waiting for my client to get the installation done.  Their IT guy has been away the last couple of weeks.  I'll touch base with them again to check on the status.

    The work around they are using for the time being is to run the query to screen and then copy/paste the results into Excel.


    Carl
    • Marked as answer by Bruce Song Monday, May 23, 2011 9:23 AM
    Wednesday, May 18, 2011 1:11 PM
  • Hi Carl,

    I have temporaily marked your last reply as the answer.

    If you still encountered the problem after installing the full Office 2007 suite, just feel free to let us know.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, May 23, 2011 9:23 AM
  • Resolved:

    Installing the full Office 2007 suite, and hence Excel 2007 corrected the problem and the client reports that it is working fine.


    Carl
    Wednesday, May 25, 2011 2:14 PM
  • Hi Carl,

    Thank you for your reponse.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 26, 2011 8:04 AM