Results 1 to 3 of 3
  1. #1
    DOCBigSky is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2017
    Posts
    5

    Handling null values in calculations and suppressing zero values in views

    I have a product inventory database I've been using for many years. I'm working on a SharePoint version so access by multiple users is easier. My old database has been tweaked and duct taped together over the years, so I am exporting out the current data and using that to make a fresh start.

    One of the main functions of the app is to tally a monthly inventory and return totals on four departments. Not every item is on hand in all locations when any given inventory is taken.

    In my old DB, I had an append query that would take all the items in the item table flagged "active" in the master list and append them to a new table with fields for various inventory locations. I know null values prevent calculation, so each field had a default value of zero, and I had it set up to suppress the zeros in the forms based on that table. Then the user could input item counts in the form, unwittingly replacing the zeros as he goes.

    I'm trying to accomplish the same thing, but I am hitting two obstacles.

    1. Since append queries are not available in Access web apps, I can't use the same method I did before to start with a "clean sheet" each month, with every field pre-populated with zeroes. I could include the inventory count fields in the item table, but I also can't do an update query to change the values back to zero each month.



    2. Even if I had zeros in all fields, I can't figure out how to suppress them in the datasheet view I have set up to enter the inventory.

    I feel like I am missing something basic.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    To handle Null values in calculations look at the Nz() function.

    I am curious, how did you suppress zeros in form?

    Since you can't run append or update queries or VBA, don't see any way to replicate your 'master' list approach in web database. User will have to manually select each item.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I am curious, how did you suppress zeros in form?
    don't know about the OP but I use the format property.

    format property for numbers has 4 sections separated by semi colons

    positive;negative;zero;null

    just a # in the first section will hide zeros if numbers are whole (or at least you don't want to display decimals), otherwise use something like

    0.00;0.00;""

    you can also specify colors which is faster than conditional formatting and is not affected by scrolling (conditional formatting is not applied until the user releases the scroll)


    [Green]0.00;[Red]0.00;"";[Blue]"N/A"

    100.00
    -100.00

    N/A

    Often use this to display some text whilst the field is blank e.g.

    [Green]0.00;[Red]0.00;"";[Red];"Please enter a value between -100 and +100"

    - you would still need validation rules to be applied, but at least user knows the limits before they enter a value

    or for text (which only has 2 options, not null and null)

    @;[Blue]"Enter City"

    there are other characters you can use for other effects.

    * will fill the width of the control with the next character so *- will fill it with hyphens
    ! will force left alignment
    > and < force text to upper or lower case respectively

    Note that the formatting property does not affect the underlying value

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

Similar Threads

  1. Null and 0 values
    By MoeIndustries in forum Reports
    Replies: 2
    Last Post: 12-04-2015, 06:39 AM
  2. Replies: 7
    Last Post: 01-12-2015, 03:47 PM
  3. Replies: 7
    Last Post: 12-04-2013, 01:55 PM
  4. Handling Duplicates and Changing Values in VBA
    By pjgoodison in forum Programming
    Replies: 5
    Last Post: 04-28-2013, 01:34 PM
  5. Finding the Max Date and Null Values if Null
    By SpdRacerX in forum Queries
    Replies: 1
    Last Post: 02-03-2012, 06:29 AM

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