none
#Name Error RRS feed

  • Question

  • I have an access 2013 DB with a problem I need an idea to solve.

    I have a tabbed form, thanks to the DBGuy.  It has quite a few tabs, each tab has a subform on it to display different data that is generated by a cross tab query.  My problem is that the db gets updated daily by importing some data.  My cross tab query  does not always contain all the same column headings, but potentially could.

    For example, tab one contains form that the cross tab query output the names of 5 columns, where tab two's cross tab query only output the names of 3 columns.  Each sub report is configured to be allowed to display the data.

    As a result, the tabs that are missing references to the missing fields (That might be there tomorrow) display a #Name error.

    I thought maybe I could use conditional formatting to hide that by turning the fore and back colors to white.  The #Name error appears even though I set both fore and back color to white.  The fields that are present will contain a value of 1 or greater.  I cannot get this method to work, as I don't know what criteria to put in the conditional format to identify the #Name value.  Any help?  Is there a better way to do it?


    • Edited by tkosel Sunday, January 28, 2018 4:27 PM
    Sunday, January 28, 2018 4:20 PM

Answers

  • The clause is added to the end of the SQL statement:

    PIVOT QueryForRmsSurgical.License
    IN("SomeColumHeader","SomeOtherColumnHeader", and so on);

    The value list within the parentheses must be exactly the values which the query can return as column headings.  They do not need to be in the order which they would normally be returned, however, so can be changed to a more logical order if necessary, e.g. where the values are month names they can be entered as ("January", "February", etc….., "December") to return the columns in correct chronological order rather than in alphabetic order.

    Ken Sheridan, Stafford, England

    Sunday, January 28, 2018 10:42 PM

All replies

  • Add an IN clause to the crosstab query so that it returns all possible columns regardless of the data, you can then bind controls in the form to each column.  Those which don't return a column will be empty.

    Ken Sheridan, Stafford, England

    Sunday, January 28, 2018 5:42 PM
  • Ken,

    Thanks for your  quick response.  I don't know how to do that.  Query is below, can you tell me where to put it?

    TRANSFORM Count(QueryForRmsSurgical.DomainName) AS CountOfDomainName
    SELECT QueryForRmsSurgical.DisplayName, QueryForRmsSurgical.LastName, QueryForRmsSurgical.FirstName, QueryForRmsSurgical.DomainName, Count(QueryForRmsSurgical.DomainName) AS [Total Of DomainName]
    FROM QueryForRmsSurgical
    GROUP BY QueryForRmsSurgical.DisplayName, QueryForRmsSurgical.LastName, QueryForRmsSurgical.FirstName, QueryForRmsSurgical.DomainName
    PIVOT QueryForRmsSurgical.License;

    Sunday, January 28, 2018 6:08 PM
  • The clause is added to the end of the SQL statement:

    PIVOT QueryForRmsSurgical.License
    IN("SomeColumHeader","SomeOtherColumnHeader", and so on);

    The value list within the parentheses must be exactly the values which the query can return as column headings.  They do not need to be in the order which they would normally be returned, however, so can be changed to a more logical order if necessary, e.g. where the values are month names they can be entered as ("January", "February", etc….., "December") to return the columns in correct chronological order rather than in alphabetic order.

    Ken Sheridan, Stafford, England

    Sunday, January 28, 2018 10:42 PM
  • Ken,

    Thanks for your answer.  I think you may have answered that one for me once before.  I guess I need to start writing this stuff down, as I tend to use it so infrequently and tend to forget!

    On a related note, what is the best way to use the results of a crosstab query in a report?  I know how to create a report using the crosstab query, but what about accomodating columns that are either new or not present in the latest query?  Is there such a thing as a crosstab report?

    Monday, January 29, 2018 5:57 PM

  • On a related note, what is the best way to use the results of a crosstab query in a report?  I know how to create a report using the crosstab query, but what about accomodating columns that are either new or not present in the latest query?  Is there such a thing as a crosstab report?

    I usually try to avoid the use of a crosstab query at all where a report is the output medium.  Often the same result can be obtained by embedding two multi-column subreports, each with across-then-down column layout, in a parent report.  The first subreport generates the column headings, the second subreport generates the data for the columns.  The 'row headings' are generated by the parent report.  As everything is data driven, the problem of column headings changing over time never arises.

    You'll find example in Payments.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file shows how to report weekly payments by customers in a crosstab-like layout.  It includes versions for single or multiple weeks.  In the latter each week's payments appear below the pervious weeks, so the data can always fit within a standard page width (A4 in this case) no matter how many weeks re reported.

    The StudentLog demo in the same OneDrive folder includes an attendances report designed in the same way, in this case with courses as the column headings.

    For true crosstab queries, there are solutions which will amend a report's column headings dynamically on the basis of the data, but while I've tried some of them out in then past, I've never had occasion to use them in my own work.  A bit of googling should easily find them.  Others here can probably provide you with links.


    Ken Sheridan, Stafford, England

    Monday, January 29, 2018 6:25 PM
  • Ken,

    As usual, your information and examples are great, thanks so much.  While I like your concept, I don't think I can use it, due to the fact that I will never know how many columns their might be.  I will also take your advice and google for more information

    Monday, January 29, 2018 8:05 PM