Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab Query Question (97)

    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.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Crosstab Query Question (97)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Question (97)

    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.

  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Question (97)

    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");

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Crosstab Query Question (97)

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

    Using the brackets tells Access you are referring to a fieldname and not a value of 2003.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Question (97)

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

  7. #7
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Question (97)

    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...

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Crosstab Query Question (97)

    >>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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Question (97)

    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... [img]/forums/images/smilies/wink.gif[/img]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •