Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-18-2017, 03:41 AM   #1
brucerblack
Newly Registered User
 
Join Date: Jul 2017
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
brucerblack is on a distinguished road
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!


imgur . com/a/k6j5E (sorry cant post links yet )

brucerblack is offline   Reply With Quote
Old 07-18-2017, 03:48 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,514
Thanks: 36
Thanked 3,073 Times in 2,987 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: If query result is empty, put 0 as default value

all depends how you are running the query and displaying the results- if as a query, don't think it can be done. If using a recordset or form, you can have code to display a zero if there are no values - or perhaps use the format property.

If you zip the image you should be able to upload it
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-18-2017, 03:49 AM   #3
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,063
Thanks: 37
Thanked 483 Times in 448 Posts
ridders will become famous soon enough ridders will become famous soon enough
Re: If query result is empty, put 0 as default value

Please give details of the code you used

This question has come up more than once before - try using the Search button on this site

For example, see this threadhttps://www.access-programmers.co.uk...d.php?t=294537

This is part of my reply to that thread:

Quote:
One way of doing this is a UNION query which joins together 2 queries

The first part is the INNER JOIN query you started with to get the results with a count.
The second part is a modified version of the unmatched query but replacing the field with nulls with Nz(YourField,0)
HTH

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!

Last edited by ridders; 07-18-2017 at 03:54 AM.
ridders is offline   Reply With Quote
Old 07-18-2017, 05:09 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,514
Thanks: 36
Thanked 3,073 Times in 2,987 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: If query result is empty, put 0 as default value

cross posted here http://www.accessforums.net/showthread.php?t=67118

@bruce: please read this link about the etiquette for cross posting http://www.excelguru.ca/content.php?184
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-18-2017, 08:07 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,514
Thanks: 36
Thanked 3,073 Times in 2,987 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: If query result is empty, put 0 as default value

any responders - now answered on the other forum

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
if there is no result in query need to have default result zero Shauqeen Queries 5 10-13-2013 09:09 PM
Query result - Default value prabhu Queries 6 11-02-2010 12:19 PM
Unusual result from empty XML nodes 303factory Modules & VBA 3 08-23-2010 03:16 AM
How to determine an empty recordsource result? Lierduh Modules & VBA 4 06-16-2003 03:04 PM




All times are GMT -8. The time now is 06:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World