none
query/report problem RRS feed

  • Question

  • I have an access 2013 application.

    I have the following query which works fine I think.  Gives me the data I am after.

    PARAMETERS [Forms]![ReportMenu]![StartDate] DateTime, [Forms]![ReportMenu]![EndDate] DateTime;
    TRANSFORM Sum(DataEntryTable.Amount) AS SumOfAmount
    SELECT DataEntryTable.InputDate, Sum(DataEntryTable.Amount) AS [Total Of Amount]
    FROM DataEntryTable
    GROUP BY DataEntryTable.InputDate
    ORDER BY DataEntryTable.InputDate
    PIVOT "C" & DataEntryTable.[G/LCode] In ("C214106-95-90","C214108-95-90","C214113-95-90","C214107-95-90","C214114-95-90");

    I have a report which uses the following record source.

    SELECT TaxReportCrosstabQuery.InputDate, TaxReportCrosstabQuery.[Total Of Amount], TaxReportCrosstabQuery.[C214106-95-90], TaxReportCrosstabQuery.[C214108-95-90], TaxReportCrosstabQuery.[C214113-95-90], TaxReportCrosstabQuery.[C214107-95-90], TaxReportCrosstabQuery.[C214114-95-90]
    FROM TaxReportCrosstabQuery;

    When I try to save the report, I get the following message.

    Help?

    Saturday, September 16, 2017 12:04 AM

Answers

  • Leo and others,

    Well nothing worked.  I cannot save the changes no matter what I do.  I am beginning to hate cross tab queries, seems as if I can never make them work quite the way I want or feel they should work!

    I have a clumsy work around.  I created a report with a sub report for each column that I was trying to use the cross tab query to create.  It isn't very elegant, but seems to work fine in my tests.

    Thanks to everyone for all your help.

    • Marked as answer by tkosel Thursday, September 21, 2017 3:44 PM
    Thursday, September 21, 2017 3:43 PM

All replies

  • Hi,

    The error message seems to indicate you tried to save the report by giving it the whole SQL statement as its name. Is that what happened?

    Saturday, September 16, 2017 12:50 AM
  • Thanks for your as usual prompt reply.  Wish I knew you as something other than the DBguy.  All I did was to create the report and build it's data source.  After I build the data source and told it to save the changes,  I got the error.  My name is Tom.

    Saturday, September 16, 2017 12:59 AM
  • How are you trying to save the report? Are you using Access menus or code?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, September 16, 2017 12:32 PM
  • How are you trying to save the report? Are you using Access menus or code?

    Paul ~~~~ Microsoft MVP (Visual Basic)


    I am using Access menus.
    Saturday, September 16, 2017 12:58 PM
  • Hi Tom, My name is Leo. If the crosstab query works, what happens if you select the query from the Nav Pane and then click on the Report button in the Create tab on the Ribbon? If you didn't get an error, try modifying the new report and then try to save it to see if you get an error. Let us know what happens.
    Saturday, September 16, 2017 1:46 PM
  • So you are doing File...Save As...Save Object As...then what? Ore, are you using one of the export buttons on the Ribbon? What format are you saving to?

    You should see a dialog box after that indicating what the file name should be. You need to make sure it's a valid file name.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, September 16, 2017 1:51 PM
  • Hi Tom,
    You could generate a query manually based on TaxReportCrosstabQuery and then use the new query to create a report.
    Beside, you could loop to test select .. From TaxReportCrosstabQuery to check if specific field cause the error
    Such as
    SELECT TaxReportCrosstabQuery.InputDate From TaxReportCrosstabQuery
    SELECT TaxReportCrosstabQuery.[Total Of Amount] From TaxReportCrosstabQuery
    SELECT TaxReportCrosstabQuery.[C214106-95-90] From TaxReportCrosstabQuery
    ...
    Best Regards,
    Terry
    Monday, September 18, 2017 6:50 AM
  • Leo and others,

    Well nothing worked.  I cannot save the changes no matter what I do.  I am beginning to hate cross tab queries, seems as if I can never make them work quite the way I want or feel they should work!

    I have a clumsy work around.  I created a report with a sub report for each column that I was trying to use the cross tab query to create.  It isn't very elegant, but seems to work fine in my tests.

    Thanks to everyone for all your help.

    • Marked as answer by tkosel Thursday, September 21, 2017 3:44 PM
    Thursday, September 21, 2017 3:43 PM
  • It can be rather difficult to help when you don't respond to questions.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, September 21, 2017 7:29 PM
  • Hi Tom,

    Glad to hear you found a workaround for your problem.

    Take a look at UtterAccess' Code Archive to see how difficult/easy it is to use crosstab queries in reports.

    Good luck with your project.

    Thursday, September 21, 2017 7:46 PM