Solved Count and Display N/A

[Valient]

Hi Team,

Can you please help me.

I'm using the code : =Count([ProductA])

To count all ProductA. However, when there is no ProductA in the list it display zero (0).
Can someone help me to improve my code to display N/A instead of zero when no ProductA available

 

[plog]

I'm surprised you get any results--Count is not a function in Access. It is a function in SQL, however your code is clearly not that. Most likely you want to use DCOUNT:

MS Access: DCount Function

This MSAccess tutorial explains how to use the Access DCount function with syntax and examples. The Microsoft Access DCount function returns the number of records from an Access table (or domain).

www.techonthenet.com

Check that link out it tells you exactly how to use the function.

However, I fear you have bigger issues if you have a field called 'ProductA'. Most likely you have not set up your tables correctly. Field names should not be that specific. The 'A' part shoudl go into a field named 'ProductType' and then whatever you are currently storing in the field 'ProductA' should go into a different field.

Before you move on to solving this count issue, I suggest you complete and post a screenshot of the Relationship Tool in Access so we can help you properly strucutre this database.

 

[Valient]

I'm surprised you get any results--Count is not a function in Access. It is a function in SQL, however your code is clearly not that. Most likely you want to use DCOUNT:

MS Access: DCount Function

This MSAccess tutorial explains how to use the Access DCount function with syntax and examples. The Microsoft Access DCount function returns the number of records from an Access table (or domain).

www.techonthenet.com

Check that link out it tells you exactly how to use the function.

However, I fear you have bigger issues if you have a field called 'ProductA'. Most likely you have not set up your tables correctly. Field names should not be that specific. The 'A' part shoudl go into a field named 'ProductType' and then whatever you are currently storing in the field 'ProductA' should go into a different field.

Before you move on to solving this count issue, I suggest you complete and post a screenshot of the Relationship Tool in Access so we can help you properly strucutre this database.

Thanks for the response.

I put the code in Control Source, Under Data.

 

[Valient]

Thanks for the response.

I put the code in Control Source, Under Data.

The system doesnt allow me to attached the screenshot

 

[MarkK]

You can format the textbox. Check out this link: https://support.microsoft.com/en-au...cy-field-e48f2312-67f0-4921-aca0-15d36b7f9c3b

The format of a textbox has four sections, first for positive numbers, second for negative numbers, third for zero, fourth for null.

 

[plog]

I think I get it now. Count will work in the headers and footers of reports/forms.

What you need to do is employee and IIF statement instead of the count:

=if(Count([ProductA])=0, "N/A", Count([ProductA]))

I still think you need to evaluate your table structure though.

 

[Valient]

I think I get it now. Count will work in the headers and footers of reports/forms.

What you need to do is employee and IIF statement instead of the count:

=if(Count([ProductA])=0, "N/A", Count([ProductA]))

I still think you need to evaluate your table structure though.

Hi plog,

You are right, I did the code in report.
I tried the suggested code but it display the word "%NAMES" not the target "N/A"

 

[plog]

I think I missed an i. Try this:

=iif(Count([ProductA])=0, "N/A", Count([ProductA]))

 

[MarkK]

You can just use =Count([FieldName]) as the ControlSource, and then edit the Format property of the textbox, which provides four-part formats for positive; negative; zero; null values that textbox may contain.

 

[Valient]

I think I missed an i. Try this:

=iif(Count([ProductA])=0, "N/A", Count([ProductA]))

Thank you.

I tried the code but it pop up with missing symbol, however when I change the "iif" to "IIf" it works.

=IIf(Count([ProductA])=0, "N/A", Count([ProductA]))

 

[Gasman]

iif works just fine, so no idea as to what you were doing?

? iif(Date = date,"True","False")
True

 

[Valient]

iif works just fine, so no idea as to what you were doing?

? iif(Date = date,"True","False")
True

This one doesnt work: =iif(Count([ProductA])=0, "N/A", Count([ProductA]))

This works: =IIf(Count([ProductA])=0, "N/A", Count([ProductA]))

Also, I'm not an expert to this access field. The DB is created by an expert guy and there is no more support.
I'm just adding more function and thanks to this forum who helps me a lot.

 

[CJ_London]

vba is not case sensitive so there should be no difference between 'iif' and IIf'

It is generally a bad idea to mix datatypes in the same column, plus in this example you are executing count twice so will have a (possibly small) hit on performance.

Personally I would use the format property as MarkK suggested in post #9 - it would be

;;"N/A"

 

[Valient]

Thank you all for your valuable suggestions.