Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Force a form to show null values (Access 2000)

    Hi all,
    I have a report that I submit daily, it's about 14 pages long, and I do it in excel. I have a crosstab query in my database from which I derive all the information, and then I transfer it to excel. The reason right now that I'm *stuck* with excel is because the boss doesn't want the pages to change as groups increase/decrease in size. My group has up to 8 possible products in it, depending on the location of the product (what the report is sorted by in the first place). I want to display all products at every location, even if there is none of it at that location. Help?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

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

    Re: Force a form to show null values (Access 2000)

    If the products are the columns in the crosstab query, you can force them all to be displayed by listing them in the Column Headers property of the query (separated by commas)

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force a form to show null values (Access 2000)

    Hans,
    I hope I didn't mislead you, as I said Force a form in the subject where I should have said force a Report. I think we may have a small misunderstanding. I haven't been able to access my database yet (it's on a development computer, it's not on the net...backwards, I know), so is the Column Headers property able to be listed on a report?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Force a form to show null values (Access 2000)

    The other thing you can do is use the NZ function to convert null values to zero.
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Force a form to show null values (Access 2000)

    You can put the column headings into the crosstab query, then they appear in the report.

    Open the query in design view, and you will colum headings in the properties box, when it displays the properties of the query.
    Regards
    John



  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force a form to show null values (Access 2000)

    This is pretty complex (to me at least), maybe it's extremely simple and I'm reading too much into it. Is there a way for me to manually design the report and refer to specific values in the crosstab? How would I refer to that value from the query? I mean, I figure my Control Source for one specific item would look something like:
    =SUM([qry]![qryLocbyCo]![location] & "BUFFALO") AND ([qry]![qryLocbyCo]![PRODUCT] & "WIDGET") AND ([qry]![qryLocbyCo]![RSTAT] & "ME")

    However I know I'm wrong...so any help would be appreciated [img]/forums/images/smilies/biggrin.gif[/img]
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Force a form to show null values (Access 2000)

    You are making it much more complex than it needs to be.

    If you put column headings into a crosstab query, the column headings show up as field names when you go to design a report.

    Then you just add them as you would any other fields.
    Regards
    John



  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Force a form to show null values (Access 2000)

    I attach a demo of a simple crosstab report.

    I have used the NZ function for the first two columns, but not for the other two.

    I find that NZ converts the value to a string, so I then put a Val function around it to turn it back into a number.
    Regards
    John



  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force a form to show null values (Access 2000)

    John,
    That example is good, however now maybe I can explain my problem. Take Penny Black and have her order nothing, no bread, no butter, etc... I still need her to show up on the report. Yes, my boss is <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Force a form to show null values (Access 2000)

    I attach another version of my example.

    Penny Black has bought nothing, and I have added another product coffee. No one has bought coffee at all.

    Yet both show up in the report.

    Three things make this work:

    * IN the query design foced teh query to show all people. Double click the join line and choose :All People", then need to do the same with join from Peopleproducts to Products. The arrows need to run all the same way.

    * Added coffee to the column headings

    * Used NZ to replace nulls with zeros in the report.
    Regards
    John



  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force a form to show null values (Access 2000)

    John,
    Beautiful solution, but you know...I always I have a wrench. All that data is on one table. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Force a form to show null values (Access 2000)

    John's report is based on a query that combines data from three tables, so please explain exactly what you mean.

  13. #13
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force a form to show null values (Access 2000)

    Hans,
    John's query works because in his join he specifies that the query must show ALL values from one of the tables. I have one table, with all of those columns on it. I want my report to show each location on a separate page. On each page, I want every possible person listed. My query (As it stands now) has two column headers (Location, and Person), and the Row header is (Product)....hopefully that provides more insight.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Force a form to show null values (Access 2000)

    I am sorry I don't understand either.

    If the people are present in the one table you have, and you don't have a separate people table, how is the system supposed to know who are these people who haven't done anything, and presumably the product that hasen't been bought?
    Regards
    John



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

    Re: Force a form to show null values (Access 2000)

    Don't you mean that Location and Person are the Row Header fields and Product is the Column Header field? A crosstab query can have only one Column Header field.

Page 1 of 2 12 LastLast

Posting Permissions

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