Error Message Result In Count Formula

[EHittner]

I have a form with a header and detail section. In the header, I created an unbound field that I want to display a count of only certain types of records in the detail. This has worked for me before, but for some reason, when I copy it to a new form, it only comes up with a "#Error" result.



The formula I'm using is: =Count(IIf([FormNumber]="551",1,0))

The list in the detail has a few different "FormNumber" types, and I'd like to keep a separate count of each.

Any ideas as to why this gremlin keeps popping up on me?

NOTE: I failed to update my profile. I'm using Access 2010 on Windows 7 (both 64 bit).

[Dal Jeanis]

One of the stupid things about Access is that, when you copy a form, sometimes it will rename a control to a generic name, if there's no code referencing the control. So, check the name of the relevant control on your fresh copy of the form, and if it has been changed to something else (like "Text9"), then change it back to [FormNumber]. Your Count will then work.

The way I've found to prevent that auto-rename is a cheat - I put a subroutine in the form's module that does a debug.print on a value of a single property of each control. I never use the subroutine for anything, but Access doesn't know that. Thus, the controls always keep the name that I gave them.

[EHittner]

I wish that were the case but, I've checked the control name and it didn't change with the copy. I've even tried re-recreating a fresh field with a new control name and it still doesn't want to count. Not sure what to do.

[EHittner]

I figured it out. Had a formula in another field in the same section that was throwing it off.

Thanks Dal Jeanis for the help. As always, I did learn something new in the course of correction.

[Dal Jeanis]

cool. Please mark thread solved. top of page, under "thread tools".