Results 1 to 6 of 6
  1. #1
    ARickert's Avatar
    ARickert is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Kansas
    Posts
    22

    Get the 0's gone and the -1's showing as 1

    I was using the 0;-0;"" format to get rid of the 0's in my table, so that I could see at a glance where my -1's where... However when it counts (totals) down at the bottom, even though the 0's arn't showing up, they are still being counted. Is there a way I can just query the table, then have the 0's gone and the absolute value of the 1 there so I can just use the sum function and don't have to worry about it?

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    hmmmm...

    are you using the summation sign in the query builder?

    what is '0;-0'""??? is that a mask of somekind?

    if you're counting the number of values in a column, COUNT() is influenced by records, not by the existence of non-null values, so that is right.

    what does absolute value have to do with this issue?

    this field that you mention is of WHAT data type?

  3. #3
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Hoping you might clear up a little confusion where you say:

    "...However when it counts (totals) down at the bottom,..."

    You were previously referring to a table; is this still a table issue? I'm not familiar with Access tables keeping "totals" at the bottom.

    Yes, you can very easily query the table to find the sum. Post a sample?

    Cheers,

    Hi Adam, - yes, the 0;-0;"" is the field format setting in the table to hide the zeros.

  4. #4
    ARickert's Avatar
    ARickert is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Kansas
    Posts
    22
    The table will keep totals at the bottom (as will the query) and the count function counts how many values are in a particular column. The 0;-0;"" eleminates the 0's from being visible, however is not completely eliminating them, so when they count they are counting the non visable 0's. The 0;-0;"" goes in the format section, and just doesn't show the 0's. I hope this is explaining everything, I'm not 100% sure what it does, how it does, or what you're both asking. I just want the -1's to show when I check a box on the form and the 0's not to show or count in the totals. If you know how to accomplish that I really do appriciate any/all help.

  5. #5
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Any chance you could post a sample so we can see what you're referring to?

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by ConneXionLost View Post
    Hi Adam, - yes, the 0;-0;"" is the field format setting in the table to hide the zeros.
    learned something else. kudos to you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form not showing
    By piper in forum Forms
    Replies: 18
    Last Post: 04-15-2011, 12:44 PM
  2. Showing data from different queries
    By samefilip in forum Reports
    Replies: 7
    Last Post: 01-06-2011, 11:09 AM
  3. Lists not showing all columns?
    By HMEpartsmanager in forum Forms
    Replies: 6
    Last Post: 11-18-2009, 03:39 PM
  4. Subform not showing correctly
    By ricardo9211 in forum Forms
    Replies: 1
    Last Post: 08-27-2009, 07:49 AM
  5. Replies: 0
    Last Post: 12-11-2008, 02:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums