Displaying Zeros Instead of Null

[brendandaly]

I've got a query that populates a text box on my switchboard with a number of records incomplete. The problem is, when that count is zero, the whole switchboard comes up blank.In reading these forums ive figured out that the Nz([],0) function is what is going to solve this, however I cant figure it out. I've tried "CountofMaster_Key: Nz((Count([Master_Key])),0)" and "CountofMaster_Key: Count(Nz([Master_Key],0))" and a couple of other variations of those but just can not get that damn field to have a damn zero in it. Thanks in advance!

 

[Trevor G]

Look at the syntax

=Sum(Nz(Price, 0) * Nz(Quantity, 0))

 

[brendandaly]

I tried Count(Nz([Master_Key],0)) and it still didnt work. Am i missing something?

 

[vbaInet]

I can see that you created this thread in the Forms section, but the syntax you have is for Queries. Are talking about summing in a control on a form or in a query?

 

[brendandaly]

I'm populating a field on an form from a query. Would it be easier/work at all if I just made the control on the Form? The problem with that is there are some criteria that the count needs to fit like "complete" (which is controlled by a yes/no flag) and the type of records that are incomplete.

 

[vbaInet]

Perhaps you could upload a stripped down version of your db so we can see the scenario and provide a fix.

 

[brendandaly]

Id be happy to but I'm having a really hard time getting it to compress to less than 2MB thats SUCH a small size... In fact, I can't really get it to a smaller file at all. How do I do that? I've got the number of records in half and taken out a bunch of stuff that is working fine...

 

[vbaInet]

Did you remember to Compact & Repair?

Remove some records, remove the irrelevant objects (i.e. forms, reports etc), Compact & Repair

 

[brendandaly]

Uh... How do I do that? Sorry.... I'm using 7zip to do this.

 

[brendandaly]

WAIT! thats in access itself isnt it?

 

[vbaInet]

WAIT! thats in access itself isnt it?

That's correct!

 

[brendandaly]

Got it. So the switch board that opens up when the DB is opened comes up blank because the person who finishes Auditing the NPI's is done meaning the 'TotalIncompleteNPI' query is returning null. In the 'CountofMaster_Key:...' field in that query is where the issue is.

 

[brendandaly]

Did I boggle your mind with the idiocy of my database so much you haven't recovered from the shock yet?

 

[vbaInet]

Hehe! I haven't had the time to look at it yet

 

[vbaInet]

I've consolidated your three queries into one and called it TotalIncomplete (following your naming convention). See attached.

 

[brendandaly]

I don't even know what to say. That's incredible. I can't even express how thankful I am. I spent all day yesterday beating my head against a wall trying to figure this out and like, here it is, perfect. I never would have figured that out and was totally convinced a Nz() function was the solution. Thank you so so so much.

 

[vbaInet]

You're welcome brendandaly

 

[brendandaly]

I don't even know what to say. This is incredible. I cant even express how grateful I am. It works like a charm. I spent all day yesterday banging my head against a wall trying to figure this out and I would never have come up with using an IIF statement. Freaking brilliant. Thank you so so so so much.

 

[vbaInet]

Now you know how to do some nicer Counts

 

[brendandaly]

Hmm. My original thank you post didn't show up after I posted it so I reposted.Oops. Eh, for these beautiful queries I'll thank you twice.