Can't get a 0 to display when null

[chappy68]

I am having a small problem with null. I have a form that allows me to add questions to each selected record if I would like to. Not every record will have questions related to it. I am also showing in a text box the count of questions for each record.

Everything is working just fine except, I would like to show a "0" where there are no questions to a record. This is seemingly simple using the nz() and/or iif() functions. I can't get it to work.

Below is the formula I am using in my query field. What am I doing wrong? If there are records to count, it is working just fine. It is just when there are no records that I have problems.

I have tried nz(Count([QAsked]),0) with no luck. Below is my current attempt.

QstAsked: IIf(IsNull(Count([QAsked])),0,Count([QAsked]))

 

[Galaxiom]

Count will return zero rather than Null if there are no records.

 

[chappy68]

It doesn't seem to returning anything. So using the nz() function wasn't changing anything. When I run the query after selecting a record with no questions, it comes up blank instead of zero. Any suggestions?

 

[Brianwarnock]

Try
Qstasked: Sum(Iif(Isnull(qasked),0,1))

Brian

 

[chappy68]

Try
Qstasked: Sum(Iif(Isnull(qasked),0,1))

Brian

I tried your formula and it returns nothing. When I select a record with questions, it returns the appropriate number. If there are no questions, the result of the query is blank.

I have attached a copy of the database if that will help. User is "chapman" and password is "cbc1350".

At the main menu click "display tasks". At the "My Tasks" form change user to "All Users" and click "Expand All". The first and last record are the only records with any questions/points entered. You can see the "Point" count in the top right corner. If you click on any of the tasks other than the first and last, the point count is blank. The query in question is "qryQuestionCount".

Thanks for your help. Forgot, I am using Access 2007.

 

[boblarson]

I can't open your db here as I only have 2003 at work, but are you sure that they are null values in the field or could they maybe be empty strings? So you could try:

Qstasked: Sum(IIf(Len([qasked] & "")=0),0,1))

 

[chappy68]

I can't open your db here as I only have 2003 at work, but are you sure that they are null values in the field or could they maybe be empty strings? So you could try:

Qstasked: Sum(IIf(Len([qasked] & "")=0),0,1))

I tried the formula and it still came back empty.

I have attached a 2003 version.

 

[boblarson]

So I seem to get the values to come back if I select the proper user and have the Item selected in the treeview control on the form.

 

[chappy68]

If there are questions asked, the count is coming back correct. What I want is a "0" to appear if there are no questions asked on a selected task. Right now I get an blank instead.

 

[boblarson]

If there are questions asked, the count is coming back correct. What I want is a "0" to appear if there are no questions asked on a selected task. Right now I get an blank instead.

The way you have it set up right now, there is no way it will do what you are looking to do. You would need to use a query with an outer join from tblTaskDetails to the questions table.

 

[chappy68]

That is not the answer I wanted. I can't believe how much time I can spend on items that won't change user behavior. I thought this was just a simple nz() function and I was off to the next issue.

I appreciate the help.

 

[boblarson]

Well, the issue is basically that you can't have a zero for something that doesn't exist in the table unless there is a source to show all applicable ID's which don't exist in that second table. Just something to keep in mind.

So, for example, I can have a table which has data for transactions and there may not be any for a particular month. So, I would need a way to have all months show up regardless and that would require me to have a table of months and link that with an outer join to a month field (created in a separate query would be fine) so that all months from the months table would show up and only the data from those which had data and THEN you can use the Nz function to show 0 where there was null records.

 

[vbaInet]

There's a way of getting the query to show zero, but it's not worth the hassle so I've implemented a different approach in the attached.

 

[chappy68]

There's a way of getting the query to show zero, but it's not worth the hassle so I've implemented a different approach in the attached.

Just out of curiosity, why won't the IsError funtion work directly in the query as:

=IIf(IsError(Count([QAsked])),0,Count([QAsked]))

I tried it and it didn't work.

BobLarson,

I appreciate your response. I had not even considered the outer join. So far I was able to change the original query and it is working in my test DB. I just need to get it into the working copy of the DB.

Thank you to both of you for pushing me in a different way of thinking of the problem.

BrianWarnock,

My bad. I don't think I had supplied you with enough information so you could assist me. Thanks for the help.

 

[vbaInet]

I think Bob has covered the reason why in his last post. There's no error to catch, there's just nothing returned.

 

[chappy68]

I think Bob has covered the reason why in his last post. There's no error to catch, there's just nothing returned.

When does it become an error? Your text box formula captured it at the form level. I made the assumption that since it can be captured as an error at the form level, it could also be captured as an error at the query level.

 

[vbaInet]

When you're trying to reference a "value" of nothing is an error, for example.

 

[chappy68]

When you're trying to reference a "value" of nothing is an error, for example.

Perfect. Now I understand. Thanks for the patience.

 

[vbaInet]

No problemo!