HELP! Report doesn't recognize field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's the deal -

I have a report (based on a query). I've created several fields in the
report, only to be populated based on a certain criterion. I am then
attempting to SUM those created fields. But the Jet engine refuses to
recognize then in the Footer areas of the report. What am I missing/doing
wrong? Below is a detail of the fields and conditions under which it/they get
filled. Note that the created fields work perfectly in the Detail section of
the report...

First field (based on two fields [1] and [qpos] that are in the query)

Name: Text80
IF statement: =IIf([qpos]=23,"3-5 yrs: " & [1],[1])

Second field (the one that doesn't get recognized in the Footer sections)

Name: Text89 (in the detail, but set to VISIBLE=NO)
IF statement: =IIf([qpos]=23,[1])

So Text89 gets a value if QPOS = 23

Third field (in the group footer)

Name: Sum of 2
IF statement: =(Sum([1])-sum([Text89]))

Last field (in the report footer)

Name: 1 Grand Total Sum
IF statement: =(Sum([1])-Sum([Text89]))
-----------------------------------------------------

So, what the heck am I doing wrong, and how can I fix it? Again, QPOS and
'1' are fields in the underlying query. The TEXTxx fields are local to the
report.

THANKS!!
 
Here's the deal -

I have a report (based on a query). I've created several fields in the
report, only to be populated based on a certain criterion. I am then
attempting to SUM those created fields. But the Jet engine refuses to
recognize then in the Footer areas of the report. What am I missing/doing
wrong? Below is a detail of the fields and conditions under which it/they get
filled. Note that the created fields work perfectly in the Detail section of
the report...

First field (based on two fields [1] and [qpos] that are in the query)

Name: Text80
IF statement: =IIf([qpos]=23,"3-5 yrs: " & [1],[1])

Second field (the one that doesn't get recognized in the Footer sections)

Name: Text89 (in the detail, but set to VISIBLE=NO)
IF statement: =IIf([qpos]=23,[1])

So Text89 gets a value if QPOS = 23

Third field (in the group footer)

Name: Sum of 2
IF statement: =(Sum([1])-sum([Text89]))

Last field (in the report footer)

Name: 1 Grand Total Sum
IF statement: =(Sum([1])-Sum([Text89]))
-----------------------------------------------------

So, what the heck am I doing wrong, and how can I fix it? Again, QPOS and
'1' are fields in the underlying query. The TEXTxx fields are local to the
report.

THANKS!!
Click to expand...

Access Reports can have several different Footers.
I'll have to assume you mean the Page Footer.
See Microsoft KnowledgeBase article:
132017 "How to sum a column of numbers in a column by page"
 
Actually, it's a GROUP footer and a report footer. That help? Also, it seems
to be the conditionals that are bolixing things up. Is there a reference
article that might assist? Neither the group footer nor the report footer are
working properly for the created fields.

Dennis

fredg said:
Here's the deal -

I have a report (based on a query). I've created several fields in the
report, only to be populated based on a certain criterion. I am then
attempting to SUM those created fields. But the Jet engine refuses to
recognize then in the Footer areas of the report. What am I missing/doing
wrong? Below is a detail of the fields and conditions under which it/they get
filled. Note that the created fields work perfectly in the Detail section of
the report...

First field (based on two fields [1] and [qpos] that are in the query)

Name: Text80
IF statement: =IIf([qpos]=23,"3-5 yrs: " & [1],[1])

Second field (the one that doesn't get recognized in the Footer sections)

Name: Text89 (in the detail, but set to VISIBLE=NO)
IF statement: =IIf([qpos]=23,[1])

So Text89 gets a value if QPOS = 23

Third field (in the group footer)

Name: Sum of 2
IF statement: =(Sum([1])-sum([Text89]))

Last field (in the report footer)

Name: 1 Grand Total Sum
IF statement: =(Sum([1])-Sum([Text89]))
-----------------------------------------------------

So, what the heck am I doing wrong, and how can I fix it? Again, QPOS and
'1' are fields in the underlying query. The TEXTxx fields are local to the
report.

THANKS!!
Click to expand...

Access Reports can have several different Footers.
I'll have to assume you mean the Page Footer.
See Microsoft KnowledgeBase article:
132017 "How to sum a column of numbers in a column by page"
Click to expand...
 
Actually, it's a GROUP footer and a report footer. That help? Also, it seems
to be the conditionals that are bolixing things up. Is there a reference
article that might assist? Neither the group footer nor the report footer are
working properly for the created fields.

Dennis

fredg said:
Here's the deal -

I have a report (based on a query). I've created several fields in the
report, only to be populated based on a certain criterion. I am then
attempting to SUM those created fields. But the Jet engine refuses to
recognize then in the Footer areas of the report. What am I missing/doing
wrong? Below is a detail of the fields and conditions under which it/they get
filled. Note that the created fields work perfectly in the Detail section of
the report...

First field (based on two fields [1] and [qpos] that are in the query)

Name: Text80
IF statement: =IIf([qpos]=23,"3-5 yrs: " & [1],[1])

Second field (the one that doesn't get recognized in the Footer sections)

Name: Text89 (in the detail, but set to VISIBLE=NO)
IF statement: =IIf([qpos]=23,[1])

So Text89 gets a value if QPOS = 23

Third field (in the group footer)

Name: Sum of 2
IF statement: =(Sum([1])-sum([Text89]))

Last field (in the report footer)

Name: 1 Grand Total Sum
IF statement: =(Sum([1])-Sum([Text89]))
-----------------------------------------------------

So, what the heck am I doing wrong, and how can I fix it? Again, QPOS and
'1' are fields in the underlying query. The TEXTxx fields are local to the
report.

THANKS!!
Click to expand...

Access Reports can have several different Footers.
I'll have to assume you mean the Page Footer.
See Microsoft KnowledgeBase article:
132017 "How to sum a column of numbers in a column by page"
Click to expand...
Click to expand...

Don't try and sum an already calculated control.
Instead of =Sum([Text89])
repeat the calculation in the Group footer and sum that.

=Sum(IIf([qpos]=23,[1],0)
 
That worked perfectly (thanks!)

How might I subtract the sum of that calculation from the sum of the entire
column? For example, I have 150 total counts, but 30 of those fall within the
calculation (qpos=23). I want my grand total to reflect 150-30, or 120.

Thanks!

fredg said:
Actually, it's a GROUP footer and a report footer. That help? Also, it seems
to be the conditionals that are bolixing things up. Is there a reference
article that might assist? Neither the group footer nor the report footer are
working properly for the created fields.

Dennis

fredg said:
On Thu, 9 Dec 2004 11:19:03 -0800, Dennis wrote:

Here's the deal -

I have a report (based on a query). I've created several fields in the
report, only to be populated based on a certain criterion. I am then
attempting to SUM those created fields. But the Jet engine refuses to
recognize then in the Footer areas of the report. What am I missing/doing
wrong? Below is a detail of the fields and conditions under which it/they get
filled. Note that the created fields work perfectly in the Detail section of
the report...

First field (based on two fields [1] and [qpos] that are in the query)

Name: Text80
IF statement: =IIf([qpos]=23,"3-5 yrs: " & [1],[1])

Second field (the one that doesn't get recognized in the Footer sections)

Name: Text89 (in the detail, but set to VISIBLE=NO)
IF statement: =IIf([qpos]=23,[1])

So Text89 gets a value if QPOS = 23

Third field (in the group footer)

Name: Sum of 2
IF statement: =(Sum([1])-sum([Text89]))

Last field (in the report footer)

Name: 1 Grand Total Sum
IF statement: =(Sum([1])-Sum([Text89]))
-----------------------------------------------------

So, what the heck am I doing wrong, and how can I fix it? Again, QPOS and
'1' are fields in the underlying query. The TEXTxx fields are local to the
report.

THANKS!!

Access Reports can have several different Footers.
I'll have to assume you mean the Page Footer.
See Microsoft KnowledgeBase article:
132017 "How to sum a column of numbers in a column by page"
Click to expand...
Click to expand...

Don't try and sum an already calculated control.
Instead of =Sum([Text89])
repeat the calculation in the Group footer and sum that.

=Sum(IIf([qpos]=23,[1],0)
Click to expand...
 
Back
Top