1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: How to show zero?

Discussion in 'Business Applications' started by chrisalvin2005, Oct 14, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. chrisalvin2005

    chrisalvin2005 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    33
    I have two tables: TMovieList and TMovieRentals.
    TMovieRentals subdata sheet of TMovieList.
    I do monthly inventory to see which movies being rented out and which ones not.
    For example: if movie number M002 rented 3 different times, total of 7 days in October, i type dates in TMovieRentals so i can see totals in QOctober.
    My big headache is if a movie not rented at all (let's say M003 not rented in October) QOctober will list all movies rented except M003.
    M001, M002, M004, etc.
    I tried Nz([Out], "0") and Nz([Back], "0") for movie out and movie back columns in the query but still null values not showing
    How can i show null values?

    thanks for all your helps in advance

    chris
     
  2. jonvan

    jonvan

    Joined:
    Oct 9, 2008
    Messages:
    66
    I'm not sure I can help resolve this, but it will help others to know what software you are working with.

    I'll make the assumption that it's Access, based on the forum you posted in, but you didn't specify.
     
  3. chrisalvin2005

    chrisalvin2005 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    33
    I can't believe i forgot to mentioned it. Although read it three times before posting. Sorry about that
     
  4. jonvan

    jonvan

    Joined:
    Oct 9, 2008
    Messages:
    66
    No worries, I am not too familiar with Access 2007, but I thought I'd help clear up the post for anyone who can help. Good luck! :)
     
  5. chrisalvin2005

    chrisalvin2005 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    33
    Can anyone help me solve this problem please?
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    chris, I can help you with this, but I do not have Access 2007.
    I assume that QOctober is a Query?
    I thought that you said "i type dates in TMovieRentals so i can see totals in QOctober. ",
    but you actually want to show both those rented out and those NOT rented out?
    Do you just want to "List" the Rented/not rented or Count them?
     
  7. chrisalvin2005

    chrisalvin2005 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    33
    Thanks for helping me OBP.

    I started this project Access 2003 and just a week ago switched to 2007.

    QOctober is a query.

    After i typied dates in TMovieRentals, i can see how many days a movie rented out in QOctober.

    TMovieRentals
    Number Out Back
    M001 Oct 5 Oct 7
    M004 Oct 8 Oct 9
    M001 Oct 12 Oct 15
    M005 Oct 15 Oct 16

    A movie can be rented out 2 different times in October so i can see that movie number listed 2 times in the QOctober.

    QOctober
    Number DaysRented
    M001 2
    M001 3
    M004 1
    M005 1

    For totals dates rented for the month i use QOctoberSubtotal that shows me total number of days for the month.

    QOctoberSubtotal
    Number TotalDaysRented
    M001 5
    M004 1
    M005 1

    My problem is i can't see movie numbers of not rented out movies.
    As the above examples show M002 and M003 not listed.

    I need query to show me "0" or if possible "not rented out" statement like the one below.

    QOctoberSubtotal
    Number Total Days Rented
    M001 5
    M002 0 (or Not Rented Out)
    M003 0 (or Not Rented Out)
    M004 1
    M005 1

    I hope i am clear.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If you have an Access 2003 version can you post it as an attachment?
     
  9. chrisalvin2005

    chrisalvin2005 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    33
    right now i am using 2007 version.
     
  10. chrisalvin2005

    chrisalvin2005 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    33
    OBP, should i post as attachment anyways?
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Chris, I can't look at an Access 2007 database.
     
  12. chrisalvin2005

    chrisalvin2005 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    33
    i have access 2003 at home (at work now) so i can add attachment from home tomorrow.
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, I will look for it tomorrow.
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Chris, it may be the Query Join between the "TMovieList and TMovieRentals" that is the problem.
    Check that the Join says from the TMovieList to the TMovieRentals and that it says List all records in TMovieList and only those matching in the TMovieRentals table.
    That should list your movies regardless of whether they are booked or not.
     
  15. chrisalvin2005

    chrisalvin2005 Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    33
    Hi OBP,
    thank you so much for your help.
    When i read the "Query Join" words, literary, a light bulb turn on in my head.
    I completely missed/forgot about Joins in table/query relationships. I was using default join which is one-to-one.
    However, i needed one-to-many since a have one movie but many times that one movie is being rented out.
    When i changed that it worked so i could see movie numbers which are not rented out at all for the month.

    I just want to write a little further so someone with the similar problem, who wants to see "0" can fix their queries.

    In order to show zero or some kind of statement you need to use Nz function.
    Basically Nz([variant], "write anything you want here")

    in my case:
    Out: Nz([MovieOut], "Not Rented Out")
    or
    Back: Nz([MovieBack], "0")

    if you receive "Circular reference cause by alias... " error
    just change column title.

    In my case:
    MovieOut: Nz([MovieOut], "Not Rented Out") gave me that error.

    I solved problem changing MovieOut column title to Out.

    Also, once you typed Nz... you should notice that table section under that column disappears. Just live as it is. I don't know why that happens but query works just fine.

    However, if you assign a table you will receive

    "Syntax error (comma) in query expression..." error.

    thanks techguy forums and OBP
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/759200

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice