• Crosstab Query Question (97)

    Author
    Topic
    #359198

    I’ve created a crosstab query where only some of the fields in the columns have values. The fields with no value are showing blank or a $0.00. The tables that the query is made from have all the fields filled with a value or $0.00 so I don’t understand why I have some empty fields and some fields that are $0.00 in the query. Anyway, how do I make the fields that contain no value either blank or zero, either in the query or in the report I’m making from the query?

    Hope that makes sense. Thanks.

    Viewing 0 reply threads
    Author
    Replies
    • #537800

      I’m guessing that blanks occur when there are no records for that particular intersection of row and column. You can make them zero by using the NZ function to wrap the values you are getting from the crosstab.

      • #537907

        I stumbled onto the Nz function after posting and was playing with it. I guess the best solution is to use it at the report level but I’m having trouble making it work. The contol source in the text box for the column is 2003. I either get all zeros or error. Could you give me some examples of what is the correct format for the Nz function in this case?

        Thanks so much for your response.

        • #537916

          Here is the Query. Can anyone tell me how to insert the Nz function and make it work. I’m about ready to give up…

          TRANSFORM Sum([Cost, NBV, RV].SumOfLRESID) AS [The Value]
          SELECT [Cost, NBV, RV].[Dealer Name], Sum([Cost, NBV, RV].SumOfAEQPCOS) AS SumOfSumOfAEQPCOS, Sum([Cost, NBV, RV].MaxOfLNETINVS) AS SumOfMaxOfLNETINVS, Sum([Cost, NBV, RV].SumOfLRESID) AS SumOfSumOfLRESID
          FROM [Cost, NBV, RV]
          GROUP BY [Cost, NBV, RV].[Dealer Name]
          PIVOT Format([LTERMDA],”yyyy”);

        • #537919

          You might try: =NZ( [2003], 0)

          Using the brackets tells Access you are referring to a fieldname and not a value of 2003.

          • #537924

            I tried that and I got a #Error in the report column… other variations yield all zeros, wiping out any values. Hence my frustration.

            • #537949

              To answer my own question in case a hapless Access newbie like myself has the same problem. If you use Nz in a report text box, the control data and name must be different or you will get an error…

            • #537971

              >>the control data and name must be different or you will get an error…<<

              This is something I forget about. I'm so used to changing the names of bound controls immediately that I forget it can be a problem. Back a few years ago in a conference in Compuserve, Ken Getz emphatically pointed out to me how one should ALWAYS change the name of bound controls so they are different from the field names (I now even have a form that goes in and does that). Given the source, I figured this was good advice.

            • #537974

              I only figured it after thinking really hard about what was going on and a little trial and error. However, now when I export the report into Excel, it drops the column sums that have the Nz function in them but picks up the rest. Anyone know how to fix that? You close one door and another one opens…

    Viewing 0 reply threads
    Reply To: Crosstab Query Question (97)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: