Results 1 to 9 of 9
  1. #1
    camkam is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2018
    Posts
    3

    Unhappy How di I make a field in a report that is 0 (zero) be blank (i.e nothing there)

    I have made a form (for a quote ) which has a range of line items,...but they are not always used for every quote, and so I have (finally!! ) worked out I have to put the default to zero if I am not using those items each time to make the calulation work, otherwise the field is blank . .. But now on the report (which is going to be the quote) where it is defulted to zero on the form it now shows zero on the report. Is there a way I can hide the zero on the form ? Many thanks and hope this make sense.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Expression in textbox: =IIf([fieldname]=0,Null,[fieldname])
    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 online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    or don't populate your fields with default zeros, use the nz function in your calculations

    newvalue=nz(field1,0)* nz(field2,0)

    note that populating a field with default zeros does not stop a user from deleting the zero value which means your calculation will fail again.

    An alternative to June's solution is to use the format property for the control (leaving the control bound to the appropriate field)

    in the format property put

    0.00;;"";""

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Obviously things I don't know about format property. Can't find any documentation for that particular structure.

    I agree with Ajax suggestion. I should have thought of it. Especially since I normally don't allow 0 as default value and use the Nz() function to handle Null.
    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.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Can't find any documentation for that particular structure.
    it is not particularly well documented but here is a link


    https://support.office.com/en-us/art...4-1fe286636668

  6. #6
    camkam is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2018
    Posts
    3
    Thankyou for help. That was great to learn, however I am still stuck on my question .. i must be a bit thick!! lol .
    when I have a field on a form for example the field is called "'Total' and I put =Nz(Field1,0)+Nz(Field1,0) in the field on the form, I also put the field with the same thing on the report .. and it defults to =Nz([Field1],0)+Nz([Field1],0) which is okay HOWEVER it still shows on the report as $0.00, where as I was wanting it to show nothing at all .. does that make sense? Many thanks ... I maybe missing something?

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I was answering based on your post
    so I have (finally!! ) worked out I have to put the default to zero if I am not using those items each time to make the calulation work
    using the nz function was to overcome the use of nulls in a calculation so the null value would show as blank and the calculation will work

    however you apparently want calculated values to be blank as well if they are zero

    that can be handled with my other suggestion

    An alternative to June's solution is to use the format property for the control (leaving the control bound to the appropriate field)

    in the format property put

    0.00;;"";""
    But I am now concerned because you have said

    HOWEVER it still shows on the report as $0.00
    this implies you either have a currency field or are formatting the calculation as text by adding the $ sign

    So, enough with the guesswork and suggestions, you need to be clearer about where your calculation is (in the report recordsource? on the report itself?), what the field types are you using in the calculation and what the calculation actually is

  8. #8
    camkam is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2018
    Posts
    3
    Thankyou for your reply. I will try and explain as best I can. (Pretty basic I might add!!)
    Ok, so my Data is in a table (called JOBDATA) (some are currency and some are just numbers are all defulted to NULL) and I enter the information on a form (ENTER NEW QUOTE) in which the fields have come from my table "'jobdata"" (I put these fields on the form by going to ""Add exisiting field" ALSO I have a total field on my form which also comes from JOBDATA , but the total fields have a calucation put in them at the table . so my problem is. If I don't fill in every field and leave some blank on the FORM there is no total, and if I put a zero in every field on the form it totals okay but the report comes out with zeros. What I want is the report to have nothing on it if zero. If this is too hard to understand dont'worry _ I will try and send it to someone to look at. Im quite happy to pay someone ... I am getting so CROSS! Many thanks

  9. #9
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by camkam View Post
    Thankyou for your reply. I will try and explain as best I can. (Pretty basic I might add!!)
    Ok, so my Data is in a table (called JOBDATA) (some are currency and some are just numbers are all defulted to NULL) and I enter the information on a form (ENTER NEW QUOTE) in which the fields have come from my table "'jobdata"" (I put these fields on the form by going to ""Add exisiting field" ALSO I have a total field on my form which also comes from JOBDATA , but the total fields have a calucation put in them at the table . so my problem is. If I don't fill in every field and leave some blank on the FORM there is no total, and if I put a zero in every field on the form it totals okay but the report comes out with zeros. What I want is the report to have nothing on it if zero. If this is too hard to understand dont'worry _ I will try and send it to someone to look at. Im quite happy to pay someone ... I am getting so CROSS! Many thanks
    If you post a copy here I'm sure someone will look at it for you
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Replies: 17
    Last Post: 02-13-2015, 06:24 AM
  2. Replies: 5
    Last Post: 09-25-2014, 08:33 PM
  3. Add a blank field through a make table Query
    By SaraBanu in forum Access
    Replies: 5
    Last Post: 08-30-2013, 06:12 AM
  4. Replies: 3
    Last Post: 09-15-2010, 01:04 PM
  5. Replies: 5
    Last Post: 09-16-2009, 05:22 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