Count Function Works on Report, but Not on Form

[Walt]

Hello - I am trying to present two alternatives to a client regardng an
application that she's requested. One alternative is to preview results on a
report that she can also print and the other is to provide screen viewing
only (via forms). She'll be informed that printing from a form will not
yield very good formatting results. The screen viewing provides more of a
performance indicator dashboard tool for the executives of the company who
don't want to be bogged down with a bunch of paper reports. The screen
version of the application will have more navigational features built into it
than the report driven version will.

Nevertheless, here is the code behind a text box serving as the control
source that's placed in the report header. This code works perfectly...

=(Count(IIf([AcceptMonth]=Forms!frmPeriod!Month And
[AcceptDateFY]=Forms!frmPeriod!FiscalYear,1,Null)))

However, when I insert the same code in a text box used as a control source
in a form header, #Err is returned. Rather than inserting the control in the
report header, I've also tried inserting it in a pager header as well as the
detail section and the same #Err result is returned. I've also checked to
make sure that I don't have any redundancies in the naming of any other
controls on the form.

I can't figure out why this works on a report, but not on a form. I'd
appreciate any help that anyone can provide. Thanks.

 

[Dale Fye]

Walt,

Is your form a single record, or is it a continuous form? My thought is
that on the form, you are going to need to use the DCOUNT( ) or DSUM( )
domain function, unless you put it in the FormFooter of a continuous form.

BTW, I find it more useful to SUM 1's and 0's than to COUNT 1's and NULLs
DSUM("IIF([AcceptMonth]=" & Forms!frmPeriod.Month _
& " AND [AcceptDateFY] = " & Forms!frmPeriod.FiscalYear _
& ", 1, 0)", "yourTable")

HTH
Dale

 

[Roger Carlson]

Are you sure you're putting it in a Form Header and not a Page Header in the
form? I've made that mistake. There's a difference. I'm not absolutely
positive, but it might not work in a Page Header.

I *know* that you can't put a function like that in a Page Header/Footer in
a *Report*. On a report, it has to go in a Group Header/Footer or a Report
Header/Footer. I'm thinking this might be true in a form as well.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Walt]

Yes...I am putting it in a Form Header. I've also tried inserting it in a
Page Header, also to no avail. As I mentioned, it is working fine when
inserted in a Report Header.

Are you sure you're putting it in a Form Header and not a Page Header in the
form? I've made that mistake. There's a difference. I'm not absolutely
positive, but it might not work in a Page Header.

I *know* that you can't put a function like that in a Page Header/Footer in
a *Report*. On a report, it has to go in a Group Header/Footer or a Report
Header/Footer. I'm thinking this might be true in a form as well.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hello - I am trying to present two alternatives to a client regardng an
application that she's requested. One alternative is to preview results
on a
report that she can also print and the other is to provide screen viewing
only (via forms). She'll be informed that printing from a form will not
yield very good formatting results. The screen viewing provides more of a
performance indicator dashboard tool for the executives of the company who
don't want to be bogged down with a bunch of paper reports. The screen
version of the application will have more navigational features built into
it
than the report driven version will.

Nevertheless, here is the code behind a text box serving as the control
source that's placed in the report header. This code works perfectly...

=(Count(IIf([AcceptMonth]=Forms!frmPeriod!Month And
[AcceptDateFY]=Forms!frmPeriod!FiscalYear,1,Null)))

However, when I insert the same code in a text box used as a control
source
in a form header, #Err is returned. Rather than inserting the control in
the
report header, I've also tried inserting it in a pager header as well as
the
detail section and the same #Err result is returned. I've also checked to
make sure that I don't have any redundancies in the naming of any other
controls on the form.

I can't figure out why this works on a report, but not on a form. I'd
appreciate any help that anyone can provide. Thanks.

 

[Walt]

Dale -

The form is a single form. I'll try your suggestion on using the domain
function, but I haven't had much luck in the past with them and I try to
avoid them if possible because of their update issues when data is refreshed.
But I will try since I'm not getting anything at all the other way. I'll
try later after I get back from lunch and will let you know how I make out.

Thanks for the quick response and suggestions.

Walt

Walt,

Is your form a single record, or is it a continuous form? My thought is
that on the form, you are going to need to use the DCOUNT( ) or DSUM( )
domain function, unless you put it in the FormFooter of a continuous form.

BTW, I find it more useful to SUM 1's and 0's than to COUNT 1's and NULLs
DSUM("IIF([AcceptMonth]=" & Forms!frmPeriod.Month _
& " AND [AcceptDateFY] = " & Forms!frmPeriod.FiscalYear _
& ", 1, 0)", "yourTable")

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hello - I am trying to present two alternatives to a client regardng an
application that she's requested. One alternative is to preview results on a
report that she can also print and the other is to provide screen viewing
only (via forms). She'll be informed that printing from a form will not
yield very good formatting results. The screen viewing provides more of a
performance indicator dashboard tool for the executives of the company who
don't want to be bogged down with a bunch of paper reports. The screen
version of the application will have more navigational features built into it
than the report driven version will.

Nevertheless, here is the code behind a text box serving as the control
source that's placed in the report header. This code works perfectly...

=(Count(IIf([AcceptMonth]=Forms!frmPeriod!Month And
[AcceptDateFY]=Forms!frmPeriod!FiscalYear,1,Null)))

However, when I insert the same code in a text box used as a control source
in a form header, #Err is returned. Rather than inserting the control in the
report header, I've also tried inserting it in a pager header as well as the
detail section and the same #Err result is returned. I've also checked to
make sure that I don't have any redundancies in the naming of any other
controls on the form.

I can't figure out why this works on a report, but not on a form. I'd
appreciate any help that anyone can provide. Thanks.