Form field default to 0 with no records

[shank]

I have a form field whose source is a query that counts the number of merchant names. If there are more than 0 records, no problem. However, if there are no records I cannot get it to default to the number 0. The form is blank. How can I get a default zero 0 in the form field if there are no records?

Setting Default value to 0 or =0 didn't work.



These IIF statements didn't work:
=IIf([qry_Test_For_Photo]![MerchantCount]="",0,[qry_Test_For_Photo]![MerchantCount])
=IIf([qry_Test_For_Photo]![MerchantCount] Is Null,0,[qry_Test_For_Photo]![MerchantCount])

thanks!

[JoeM]

Try using the NZ function, which handles Nulls, i.e.

=NZ([qry_Test_For_Photo]![MerchantCount],0) + 0

[shank]

That didn't work. I think it has something to do with their not being any records at all as opposed to dealing with a null query field. Even if I hard code something like =4 I still get a blank form. Again, if my query counts 1 or more records, no problem.

So, how do I get the form field to display a 0 if there are no records at all?

thanks!

[JoeM]

Try doing the Count calculation in your query itself (instead of directly on the Form), using the NZ function there.
The NZ function handles the case of a null entry due to no matches being found.
If you run into issues, post the SQL code of your query.

[shank]

I tried these 2 queries and cannot get a 0 in the results.

SELECT TOI.NAME, Nz([NAME],0)+0 AS MerchantCount
FROM TOI
GROUP BY TOI.NAME
HAVING (((TOI.NAME)="Photo"));
... or ....
SELECT Count(Nz([NAME],0)+0) AS MerchantCount
FROM TOI
GROUP BY TOI.NAME
HAVING (((TOI.NAME)="Photo"));

thanks!

[JoeM]

Let's take a step back, and let's talk about your structure.
I was assuming that if you have a situation where you are trying to count something that doesn't exist, two tables would be involved. But I only see one in your query attempts.
Can you please explain your structure, the tables, and queries involved?
A small data sample of each table involved might be helpful.

[rpeare]

=iif(dcount("*", "Qry_Test_For_Photo") = 0, 0, [qry_test_for_photo]![MerchantCount])

I'm not sure this will work as a control source but whenever you requery your form you could do something like

[MERCHANTCOUNT] = iif(dcount("*", "Qry_Test_For_Photo") = 0, 0, [qry_test_for_photo]![MerchantCount])

assuming the field on your form is named MERCHANTCOUNT

[shank]

>>I was assuming that if you have a situation where you are trying to count something that doesn't exist<<
That is correct. Not two tables. Just one table.

The easiest way to replicate this is to create a table with a column named "Company". No records. Then build a query to count the number of records where Companny=ABC. Your query is going to be blank, like mine. How do I get that query to result in zero 0 ..?

In the end, I have a form whose data source is the above query. It has a field named MerchantCount. I need that field to reflect 0. I've been experimenting with DCount with no luck either.

Thanks!

[JoeM]

I am confused. If Company "ABC" is not anywhere in the table, where is the Company Code "ABC" coming from then?

[shank]

That's my point. It's not there. But instead of the query resulting in a count of null, I want a count of 0. i.e. How many records have Company=ABC? 0

[JoeM]

But you still haven't answered my question. Where is the "ABC Company" coming from?
Do you have some free form Text Box on your Form where they can enter whatever the heck they want?
If so, why not have it be a Combo Box instead that lists all the Company Codes in your table and make them select from one of those.
If they do not see one there, then they know that there are no records for it.

[rpeare]

I think we're getting confused about what your query is currently doing.

If your query has multiple rows and you have a formula in one of your columns that counts records but you are ending up with a null value in that count field because you are counting items from a field that is not populated you would have to share the formula that is running that calculation. Perhaps if you share an example of what your query actually looks like and what value you're trying to retrieve from the query that would be more helpful because there are several suggestions already on this thread that should work depending on what your query actually does.

For instance something as simple as =nz(Merchantcount, 0) would work if your merchant count field is null

[shank]

Where is the "ABC Company" coming from?

TableName = TestTable
ColumnName = Company
Data = no data, no records
Search for CompanyName = ABC

If they do not see one there, then they know that there are no records for it

I'm using the results of the form as a condition to trigger a step in a macro. Originally, I tried using the query in the macro but it would error. So I changed to using a form based on the query, hidden. If I were to populate ColumnName = Company with say 10 records, any of which have the ficticious company "ABC", the form works and triggers the macro step as expected. However, if the form is empty or null, the condition causes an error. So I'm trying to get it to populate with a 0 if there is no company "ABC" in that column.

If from field MerchantCount = 0 - no problem
If from field MerchantCount = 3 - no problem
If from field MerchantCount = Null - PROBLEM

I've tried these 2 examples of queries and still cannot get a 0 in the results.
SELECT TOI.NAME, Nz([NAME],0)+0 AS MerchantCount
FROM TOI
GROUP BY TOI.NAME
HAVING (((TOI.NAME)="Photo"));
... or ....
SELECT Count(Nz([NAME],0)+0) AS MerchantCount
FROM TOI
GROUP BY TOI.NAME
HAVING (((TOI.NAME)="Photo"));


thanks!

[JoeM]

SELECT TOI.NAME, Nz([NAME],0)+0 AS MerchantCount
FROM TOI
GROUP BY TOI.NAME
HAVING (((TOI.NAME)="Photo"));

So, are you saying that TOI is a table (and not a query), and the issue is that there are no records in the "TOI" table with Name = "Photo", which is why you are having a Null entry?
The problem is if nothing meets your criteria, there are no records to return. So you cannot return any field if there are 0 records in your query!

If this is, in fact, what is going on, I would approach this in a different.
Wherever/however "Photo" is being entered (sounds like through an unbound Text Box on your Form maybe), store this value in a single record table with just the "Name" field.
Then, you can do a LEFT JOIN from this single record table to your "TOI" table, and it will return the NAME you want in the query results, and you can use the NZ function to return a zero.

[shank]

If this is, in fact, what is going on

Yes, that is what's happening.

Below is the new query. TOI is a table. SC is a table.
TOI.NAME may or may not have the company name
SC.Merchant has a list of all the merchant names
I still get a NUll result (no records) in the query.

SELECT TOI.NAME, Count(Nz([NAME],0)) AS MerchantCount
FROM SC LEFT JOIN TOI ON SC.Merchant = TOI.NAME
GROUP BY TOI.NAME
HAVING (((TOI.NAME)="Photo"));

thanks!