Results 1 to 9 of 9
  1. #1
    themushroomking is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    23

    If query result is empty, put 0 as default value

    Hi folks! I've been looking around everywhere and get the same answer, but somehow it doesn't work.

    I run a query that has a sum total. If the total value is "nothing" i want the default value to be 0.
    I want this because i have a form that displays the calculated value, but if it's empty, my whole form goes blank (so does the query).

    I read everywhere about the Nz expression. But it doesn't return 0 in any case possible and my problem remains. Please help!


    Click image for larger version. 

Name:	null.png 
Views:	19 
Size:	4.3 KB 
ID:	29494



    What am i doing wrong here? Thanks in advance!

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You don't want it in your Criteria, you want it in a Calculated field.
    So create a calculated field like this:
    Code:
    Pallets2: NZ([inboundreturns].[pallets],0)+0
    and then Sum that field.

  3. #3
    themushroomking is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    23
    Hi there JoeM. Thanks for your swift reply Much appreciated.

    So i create a field pallets2 in my table, put "calculated" for field type, paste the code

    and it give me a syntax error? :S

    Could it be the dot? Or the , should be ; ?

    Cant seem to figure this one out. Stuck for sooooo long on this tiny little inconsitancy

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Perhaps we need to clarify something first.

    When you say:
    I run a query that has a sum total. If the total value is "nothing" i want the default value to be 0.
    Is it "nothing" because the record with "In buffer" for the Status field have blanks (empty values) in the "pallets" field?
    Or is it because there are no "In buffer" records in the table?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    your image shows

    nz("pallets,0")

    it should be

    nz([pallets],0)


    but I don't think that is the issue anyway. If a query returns nothing, it returns nothing. To show that you would need to left join from another table

  6. #6
    themushroomking is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    23
    Hi guys

    Indeed. The query returns blank because there are no pallets in the buffer (no results).
    My problem is that i have a form that shows the results of multiple queries. If one of them (in this case the buffer query) is empty, my whole form goes blank!

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Do you have a table somewhere that lists all the possible "Status" values, including "In buffer"?
    You would need something like that in order to do the Left Join that Ajax is talking about.
    If you do not have a value of "In buffer" in any table anywhere in your database, you are not going to be able to return any query containing it.

  8. #8
    themushroomking is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    23
    Thanks for all the help guys! I did it though. I just made a fake record with "in buffer" status. But i left the pallet count field empty.
    In my table i made a new field called hidden with a value yes or no. (Default value No)

    So in my query i left everything as it was. I had to make sure the fake record didnt show up anywhere else. So i filtered all other queries on value "Hidden? No".

    Im not sure if it makes sense. But it does give the right results. I just think its retarded Access cannot just put a 0 in a query field when i want to.
    Also is really stupid my entire form shows up totally blank because 1 query of many queries returns blank.
    Access...gotto hate it, but what you gonna do :P

    Thanks alot guys!

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I just think its retarded Access cannot just put a 0 in a query field when i want to.
    Also is really stupid my entire form shows up totally blank because 1 query of many queries returns blank.
    Access...gotto hate it, but what you gonna do
    It is not an Access thing, it is a basic relational database concept. You cannot summarize something which does not exist in the database.
    A simple solution is to create a single field table which lists all the possible Statuses, and then use that in a Left Join (putting your criteria on this field).

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

Similar Threads

  1. Replies: 7
    Last Post: 08-28-2014, 01:33 PM
  2. Default value is result of IF() statement.
    By dredgy in forum Access
    Replies: 4
    Last Post: 01-18-2014, 11:42 AM
  3. Catch empty search result
    By octsim in forum Programming
    Replies: 5
    Last Post: 12-02-2013, 02:23 PM
  4. When a query result is empty.....
    By khanson in forum Queries
    Replies: 3
    Last Post: 08-01-2011, 09:12 PM
  5. Controls go blank on empty query result
    By kevdfisch in forum Programming
    Replies: 4
    Last Post: 08-25-2009, 08:07 AM

Tags for this Thread

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