If query result is empty, put 0 as default value (1 Viewer)

brucerblack · Jul 18, 2017

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

)

CJ_London · Jul 18, 2017

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

isladogs · Jul 18, 2017

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/forums/showthread.php?t=294537

This is part of my reply to that thread:

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

CJ_London · Jul 18, 2017

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 · Jul 18, 2017

any responders - now answered on the other forum

If query result is empty, put 0 as default value (1 Viewer)

brucerblack

New member

CJ_London

Super Moderator

isladogs

MVP / VIP

CJ_London

Super Moderator

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread