Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Evansville, Indiana, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing null information (Access XP)

    I have a database that tracks my expenses and the credit cards that I bill them to. I want the report to show a zero if nothing was billed to a credit card for the requested time period. My report works fine except nothing shows up for the credit cards that had nothing charged to them. My report is grouped on the credit card number and prints the card number in one column and the total amount charged to that card in another column. How do I get the report to show all the card numbers and show a zero in the amount column if there were no charges for that time period? Thank you for your help.

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

    Re: Printing null information (Access XP)

    You need a separate CreditCard Table, then you need to do a Left Outer Join between this CreditCard table and your Transaction table, such that you get all records from the CreditCard table and also all records from the transaction table that match. This way, you will get at least 1 record for each Credit Card.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Evansville, Indiana, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing null information (Access XP)

    Mark,

    Thanks for your reply. I do have a credit card table and I do have the outer join. The problem is that I do not create a record when I do not charge to one of the credit card numbers. In other words, there will be no match to a transaction for that card for those dates. When I open my report, I have it request a beginning date and an ending date. It then generates a report of all credit card expenses between those dates. If nothing was charged to one of my cards between those dates, a record does not exist. How do I get Access to interpret no records for that card between those dates to be zero dollars charged to that card between those dates? Thanks for your help.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Printing null information (Access XP)

    You could either use a temp table to hold these non charges and/or use a union query.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Printing null information (Access XP)

    If you set up a query the way Mark Liquorman suggested, you should get a record for every credit card, even those without transactions. You could use the Nz function in the amount field to convert null (blank) values to 0.
    If you can't make it work, you might consider posting a stripped down copy of your database, so that Loungers can look at the problem directly. See <post#=401925>post 401925</post#> for instructions.

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Evansville, Indiana, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing null information (Access XP)

    Pat,

    Thank you for your reply. I think I will try the Nz function suggested by Hans first. If that doesn't work, I'll try your approach. Thanks again.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,430
    Thanks
    1
    Thanked 33 Times in 33 Posts

    Re: Printing null information (Access XP)

    You have to add one little thing. To the TransactionDate critieria, add an OR clause for "Is Null". So the SQL would read something like this:

    .... WHERE TransactionDate Between BeginningDate and EndingDate OR TransactionDate IS Null
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Evansville, Indiana, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing null information (Access XP)

    Hans,

    Thank you for your reply. I will take another look at my setup and make sure I did it the way Mark suggested. If it turns out to be correct, I will try your Nz function suggestion. Thanks again.

  9. #9
    Lounger
    Join Date
    Jan 2001
    Location
    Evansville, Indiana, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing null information (Access XP)

    Mark,

    Thanks for your reply. I will give this a try. Thanks again.

    Trena

Posting Permissions

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