forumula with iif then looking at total for numerous depts.


B

babs

I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 
Ad

Advertisements

B

BruceM

Did you try referencing the GrandTotal text box (the text box that displays
the Grand Total in the report footer) in the group footer? Assuming the
GrandTotal text box is named txtGrandTotal, as the Control Source of an
unbound text box in the group footer:
=[txtGrandTotal]
 
D

Duane Hookom

If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.
 
B

babs

Duane,
You have it exactly correct - I do not know where to put the formula to have
the result only display in the group footer for dept. num 4 - and not sure
how to do a Running Sum for dept 1-4??

Thanks so much,
barb

Duane Hookom said:
If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.

--
Duane Hookom
Microsoft Access MVP


babs said:
I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 
D

Duane Hookom

You would use a text box in the DeptNum group footer:
Name: txtRegPayRunSum
Control Source: =Sum([reg pay])
Running Sum: Over All
Visible: No
Then in the On Format event module/code add something like:
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 4)
This should keep the sum()s building but only display the value in the 4
deptnum.

--
Duane Hookom
Microsoft Access MVP


babs said:
Duane,
You have it exactly correct - I do not know where to put the formula to have
the result only display in the group footer for dept. num 4 - and not sure
how to do a Running Sum for dept 1-4??

Thanks so much,
barb

Duane Hookom said:
If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.

--
Duane Hookom
Microsoft Access MVP


babs said:
I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 
B

babs

Thanks Duane,
I put it in the On Load Event of the Report - correct place??

I have an issue thought the data I just ran it on had no info this week for
Dept. num 4 just 1,2,3,5 and want the running sum to show up in footer for 3
here- should show up at the last on of 1-4 where there is data for that time
period.

What code would I use - and if then? and put it in the event procedure for
the on load of the report to make the text box visible.

thanks so much,
barb

Duane Hookom said:
You would use a text box in the DeptNum group footer:
Name: txtRegPayRunSum
Control Source: =Sum([reg pay])
Running Sum: Over All
Visible: No
Then in the On Format event module/code add something like:
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 4)
This should keep the sum()s building but only display the value in the 4
deptnum.

--
Duane Hookom
Microsoft Access MVP


babs said:
Duane,
You have it exactly correct - I do not know where to put the formula to have
the result only display in the group footer for dept. num 4 - and not sure
how to do a Running Sum for dept 1-4??

Thanks so much,
barb

Duane Hookom said:
If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.

--
Duane Hookom
Microsoft Access MVP


:

I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 
Ad

Advertisements

B

babs

Duane,
I even retried it with making deptnum=3 just for this data and it is not
visible?? should it be in the on load event??

Duane Hookom said:
You would use a text box in the DeptNum group footer:
Name: txtRegPayRunSum
Control Source: =Sum([reg pay])
Running Sum: Over All
Visible: No
Then in the On Format event module/code add something like:
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 4)
This should keep the sum()s building but only display the value in the 4
deptnum.

--
Duane Hookom
Microsoft Access MVP


babs said:
Duane,
You have it exactly correct - I do not know where to put the formula to have
the result only display in the group footer for dept. num 4 - and not sure
how to do a Running Sum for dept 1-4??

Thanks so much,
barb

Duane Hookom said:
If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.

--
Duane Hookom
Microsoft Access MVP


:

I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 
D

Duane Hookom

The code would be in the On Format event of the deptnum group footer.
--
Duane Hookom
Microsoft Access MVP


babs said:
Duane,
I even retried it with making deptnum=3 just for this data and it is not
visible?? should it be in the on load event??

Duane Hookom said:
You would use a text box in the DeptNum group footer:
Name: txtRegPayRunSum
Control Source: =Sum([reg pay])
Running Sum: Over All
Visible: No
Then in the On Format event module/code add something like:
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 4)
This should keep the sum()s building but only display the value in the 4
deptnum.

--
Duane Hookom
Microsoft Access MVP


babs said:
Duane,
You have it exactly correct - I do not know where to put the formula to have
the result only display in the group footer for dept. num 4 - and not sure
how to do a Running Sum for dept 1-4??

Thanks so much,
barb

:

If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.

--
Duane Hookom
Microsoft Access MVP


:

I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 
B

babs

duane,
When I make the text box visible the running sum shows in each group what i
want. When I add the code to the On format event to the group footer it is
not showing up - i am not sure how to handle the code if the data does not
have dept 4 for that week. somethimes it may be just dept 1,2,3,5,6 etc.

but even when I changed the code =3 the cell is still not visible.
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 3)

any ideas on the code for the event procedure - where i might be wrong?

thanks,
barb

Duane Hookom said:
The code would be in the On Format event of the deptnum group footer.
--
Duane Hookom
Microsoft Access MVP


babs said:
Duane,
I even retried it with making deptnum=3 just for this data and it is not
visible?? should it be in the on load event??

Duane Hookom said:
You would use a text box in the DeptNum group footer:
Name: txtRegPayRunSum
Control Source: =Sum([reg pay])
Running Sum: Over All
Visible: No
Then in the On Format event module/code add something like:
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 4)
This should keep the sum()s building but only display the value in the 4
deptnum.

--
Duane Hookom
Microsoft Access MVP


:

Duane,
You have it exactly correct - I do not know where to put the formula to have
the result only display in the group footer for dept. num 4 - and not sure
how to do a Running Sum for dept 1-4??

Thanks so much,
barb

:

If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.

--
Duane Hookom
Microsoft Access MVP


:

I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 
D

Duane Hookom

Does the code compile?
What is the name of your text box that is bound to deptnum?
Are you deptnums numeric?

--
Duane Hookom
Microsoft Access MVP


babs said:
duane,
When I make the text box visible the running sum shows in each group what i
want. When I add the code to the On format event to the group footer it is
not showing up - i am not sure how to handle the code if the data does not
have dept 4 for that week. somethimes it may be just dept 1,2,3,5,6 etc.

but even when I changed the code =3 the cell is still not visible.
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 3)

any ideas on the code for the event procedure - where i might be wrong?

thanks,
barb

Duane Hookom said:
The code would be in the On Format event of the deptnum group footer.
--
Duane Hookom
Microsoft Access MVP


babs said:
Duane,
I even retried it with making deptnum=3 just for this data and it is not
visible?? should it be in the on load event??

:

You would use a text box in the DeptNum group footer:
Name: txtRegPayRunSum
Control Source: =Sum([reg pay])
Running Sum: Over All
Visible: No
Then in the On Format event module/code add something like:
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 4)
This should keep the sum()s building but only display the value in the 4
deptnum.

--
Duane Hookom
Microsoft Access MVP


:

Duane,
You have it exactly correct - I do not know where to put the formula to have
the result only display in the group footer for dept. num 4 - and not sure
how to do a Running Sum for dept 1-4??

Thanks so much,
barb

:

If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.

--
Duane Hookom
Microsoft Access MVP


:

I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 
B

babs

my real field name is Cost Id(not dept num) just thought it might be easier
to understand(sorry)
The name of the cost id field is txtCostID and it Is a number field - I
thought that may have been it but not so - still not visible

this is my code in the on format event of the groupfooter

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Me.txtRegPayRunSum.Visible = (Me.txtCostID = 3)
End Sub


and still don't know how to handle if no group 4 not sure of the syntax for
maybe an if then

thanks for still helping,
Barb

Duane Hookom said:
Does the code compile?
What is the name of your text box that is bound to deptnum?
Are you deptnums numeric?

--
Duane Hookom
Microsoft Access MVP


babs said:
duane,
When I make the text box visible the running sum shows in each group what i
want. When I add the code to the On format event to the group footer it is
not showing up - i am not sure how to handle the code if the data does not
have dept 4 for that week. somethimes it may be just dept 1,2,3,5,6 etc.

but even when I changed the code =3 the cell is still not visible.
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 3)

any ideas on the code for the event procedure - where i might be wrong?

thanks,
barb

Duane Hookom said:
The code would be in the On Format event of the deptnum group footer.
--
Duane Hookom
Microsoft Access MVP


:

Duane,
I even retried it with making deptnum=3 just for this data and it is not
visible?? should it be in the on load event??

:

You would use a text box in the DeptNum group footer:
Name: txtRegPayRunSum
Control Source: =Sum([reg pay])
Running Sum: Over All
Visible: No
Then in the On Format event module/code add something like:
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 4)
This should keep the sum()s building but only display the value in the 4
deptnum.

--
Duane Hookom
Microsoft Access MVP


:

Duane,
You have it exactly correct - I do not know where to put the formula to have
the result only display in the group footer for dept. num 4 - and not sure
how to do a Running Sum for dept 1-4??

Thanks so much,
barb

:

If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.

--
Duane Hookom
Microsoft Access MVP


:

I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 
Ad

Advertisements

D

Duane Hookom

You haven't told us what you want to happen if there is no CostID 4.
You didn't say if the code compiles or not.

Do you have a bound text box in the group footer with the name txtCostID
which has a control source of
[Cost ID]

--
Duane Hookom
Microsoft Access MVP


babs said:
my real field name is Cost Id(not dept num) just thought it might be easier
to understand(sorry)
The name of the cost id field is txtCostID and it Is a number field - I
thought that may have been it but not so - still not visible

this is my code in the on format event of the groupfooter

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Me.txtRegPayRunSum.Visible = (Me.txtCostID = 3)
End Sub


and still don't know how to handle if no group 4 not sure of the syntax for
maybe an if then

thanks for still helping,
Barb

Duane Hookom said:
Does the code compile?
What is the name of your text box that is bound to deptnum?
Are you deptnums numeric?

--
Duane Hookom
Microsoft Access MVP


babs said:
duane,
When I make the text box visible the running sum shows in each group what i
want. When I add the code to the On format event to the group footer it is
not showing up - i am not sure how to handle the code if the data does not
have dept 4 for that week. somethimes it may be just dept 1,2,3,5,6 etc.

but even when I changed the code =3 the cell is still not visible.
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 3)

any ideas on the code for the event procedure - where i might be wrong?

thanks,
barb

:

The code would be in the On Format event of the deptnum group footer.
--
Duane Hookom
Microsoft Access MVP


:

Duane,
I even retried it with making deptnum=3 just for this data and it is not
visible?? should it be in the on load event??

:

You would use a text box in the DeptNum group footer:
Name: txtRegPayRunSum
Control Source: =Sum([reg pay])
Running Sum: Over All
Visible: No
Then in the On Format event module/code add something like:
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 4)
This should keep the sum()s building but only display the value in the 4
deptnum.

--
Duane Hookom
Microsoft Access MVP


:

Duane,
You have it exactly correct - I do not know where to put the formula to have
the result only display in the group footer for dept. num 4 - and not sure
how to do a Running Sum for dept 1-4??

Thanks so much,
barb

:

If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.

--
Duane Hookom
Microsoft Access MVP


:

I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 
B

babs

yes there is a bound text box actually in the group header iwth the name
txtcostid with a control source of Cost Id
- if there is no data for costid 4 then the running sum should show up in 3
or if no 3 or 4 should show up in 2 if no 2 3 or 4 should show up in 1.

I am not sure if is compiling?-not exactly sure how to check - not getting
any errors?

thanks,
barb

Duane Hookom said:
You haven't told us what you want to happen if there is no CostID 4.
You didn't say if the code compiles or not.

Do you have a bound text box in the group footer with the name txtCostID
which has a control source of
[Cost ID]

--
Duane Hookom
Microsoft Access MVP


babs said:
my real field name is Cost Id(not dept num) just thought it might be easier
to understand(sorry)
The name of the cost id field is txtCostID and it Is a number field - I
thought that may have been it but not so - still not visible

this is my code in the on format event of the groupfooter

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Me.txtRegPayRunSum.Visible = (Me.txtCostID = 3)
End Sub


and still don't know how to handle if no group 4 not sure of the syntax for
maybe an if then

thanks for still helping,
Barb

Duane Hookom said:
Does the code compile?
What is the name of your text box that is bound to deptnum?
Are you deptnums numeric?

--
Duane Hookom
Microsoft Access MVP


:

duane,
When I make the text box visible the running sum shows in each group what i
want. When I add the code to the On format event to the group footer it is
not showing up - i am not sure how to handle the code if the data does not
have dept 4 for that week. somethimes it may be just dept 1,2,3,5,6 etc.

but even when I changed the code =3 the cell is still not visible.
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 3)

any ideas on the code for the event procedure - where i might be wrong?

thanks,
barb

:

The code would be in the On Format event of the deptnum group footer.
--
Duane Hookom
Microsoft Access MVP


:

Duane,
I even retried it with making deptnum=3 just for this data and it is not
visible?? should it be in the on load event??

:

You would use a text box in the DeptNum group footer:
Name: txtRegPayRunSum
Control Source: =Sum([reg pay])
Running Sum: Over All
Visible: No
Then in the On Format event module/code add something like:
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 4)
This should keep the sum()s building but only display the value in the 4
deptnum.

--
Duane Hookom
Microsoft Access MVP


:

Duane,
You have it exactly correct - I do not know where to put the formula to have
the result only display in the group footer for dept. num 4 - and not sure
how to do a Running Sum for dept 1-4??

Thanks so much,
barb

:

If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.

--
Duane Hookom
Microsoft Access MVP


:

I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 
D

Duane Hookom

I would bind a text box to the CostId in the Group Footer and reference this
in your code. Since you might not have a CostID of 4, you need to add a text
box to your Report Header section:
Name: txtMaxCostID
Control Source: =Max([Cost ID]*Abs([Cost ID]<=4))
CostID Group Footer text box:
Name: txtCostIDFooter
Control Source: [Cost ID]

Then Change your code to reference txtMaxCostID and compare to the
txtCostIDFooter:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Me.txtRegPayRunSum.Visible = Me.txtCostIDFooter = Me.txtMaxCostID
End Sub

--
Duane Hookom
Microsoft Access MVP


babs said:
yes there is a bound text box actually in the group header iwth the name
txtcostid with a control source of Cost Id
- if there is no data for costid 4 then the running sum should show up in 3
or if no 3 or 4 should show up in 2 if no 2 3 or 4 should show up in 1.

I am not sure if is compiling?-not exactly sure how to check - not getting
any errors?

thanks,
barb

Duane Hookom said:
You haven't told us what you want to happen if there is no CostID 4.
You didn't say if the code compiles or not.

Do you have a bound text box in the group footer with the name txtCostID
which has a control source of
[Cost ID]

--
Duane Hookom
Microsoft Access MVP


babs said:
my real field name is Cost Id(not dept num) just thought it might be easier
to understand(sorry)
The name of the cost id field is txtCostID and it Is a number field - I
thought that may have been it but not so - still not visible

this is my code in the on format event of the groupfooter

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Me.txtRegPayRunSum.Visible = (Me.txtCostID = 3)
End Sub


and still don't know how to handle if no group 4 not sure of the syntax for
maybe an if then

thanks for still helping,
Barb

:

Does the code compile?
What is the name of your text box that is bound to deptnum?
Are you deptnums numeric?

--
Duane Hookom
Microsoft Access MVP


:

duane,
When I make the text box visible the running sum shows in each group what i
want. When I add the code to the On format event to the group footer it is
not showing up - i am not sure how to handle the code if the data does not
have dept 4 for that week. somethimes it may be just dept 1,2,3,5,6 etc.

but even when I changed the code =3 the cell is still not visible.
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 3)

any ideas on the code for the event procedure - where i might be wrong?

thanks,
barb

:

The code would be in the On Format event of the deptnum group footer.
--
Duane Hookom
Microsoft Access MVP


:

Duane,
I even retried it with making deptnum=3 just for this data and it is not
visible?? should it be in the on load event??

:

You would use a text box in the DeptNum group footer:
Name: txtRegPayRunSum
Control Source: =Sum([reg pay])
Running Sum: Over All
Visible: No
Then in the On Format event module/code add something like:
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 4)
This should keep the sum()s building but only display the value in the 4
deptnum.

--
Duane Hookom
Microsoft Access MVP


:

Duane,
You have it exactly correct - I do not know where to put the formula to have
the result only display in the group footer for dept. num 4 - and not sure
how to do a Running Sum for dept 1-4??

Thanks so much,
barb

:

If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.

--
Duane Hookom
Microsoft Access MVP


:

I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 
Ad

Advertisements

B

babs

Got it !!!!! thanks sooo much,
barb

Duane Hookom said:
I would bind a text box to the CostId in the Group Footer and reference this
in your code. Since you might not have a CostID of 4, you need to add a text
box to your Report Header section:
Name: txtMaxCostID
Control Source: =Max([Cost ID]*Abs([Cost ID]<=4))
CostID Group Footer text box:
Name: txtCostIDFooter
Control Source: [Cost ID]

Then Change your code to reference txtMaxCostID and compare to the
txtCostIDFooter:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Me.txtRegPayRunSum.Visible = Me.txtCostIDFooter = Me.txtMaxCostID
End Sub

--
Duane Hookom
Microsoft Access MVP


babs said:
yes there is a bound text box actually in the group header iwth the name
txtcostid with a control source of Cost Id
- if there is no data for costid 4 then the running sum should show up in 3
or if no 3 or 4 should show up in 2 if no 2 3 or 4 should show up in 1.

I am not sure if is compiling?-not exactly sure how to check - not getting
any errors?

thanks,
barb

Duane Hookom said:
You haven't told us what you want to happen if there is no CostID 4.
You didn't say if the code compiles or not.

Do you have a bound text box in the group footer with the name txtCostID
which has a control source of
[Cost ID]

--
Duane Hookom
Microsoft Access MVP


:

my real field name is Cost Id(not dept num) just thought it might be easier
to understand(sorry)
The name of the cost id field is txtCostID and it Is a number field - I
thought that may have been it but not so - still not visible

this is my code in the on format event of the groupfooter

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Me.txtRegPayRunSum.Visible = (Me.txtCostID = 3)
End Sub


and still don't know how to handle if no group 4 not sure of the syntax for
maybe an if then

thanks for still helping,
Barb

:

Does the code compile?
What is the name of your text box that is bound to deptnum?
Are you deptnums numeric?

--
Duane Hookom
Microsoft Access MVP


:

duane,
When I make the text box visible the running sum shows in each group what i
want. When I add the code to the On format event to the group footer it is
not showing up - i am not sure how to handle the code if the data does not
have dept 4 for that week. somethimes it may be just dept 1,2,3,5,6 etc.

but even when I changed the code =3 the cell is still not visible.
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 3)

any ideas on the code for the event procedure - where i might be wrong?

thanks,
barb

:

The code would be in the On Format event of the deptnum group footer.
--
Duane Hookom
Microsoft Access MVP


:

Duane,
I even retried it with making deptnum=3 just for this data and it is not
visible?? should it be in the on load event??

:

You would use a text box in the DeptNum group footer:
Name: txtRegPayRunSum
Control Source: =Sum([reg pay])
Running Sum: Over All
Visible: No
Then in the On Format event module/code add something like:
Me.txtRegPayRunSum.Visible = (Me.txtDeptNum = 4)
This should keep the sum()s building but only display the value in the 4
deptnum.

--
Duane Hookom
Microsoft Access MVP


:

Duane,
You have it exactly correct - I do not know where to put the formula to have
the result only display in the group footer for dept. num 4 - and not sure
how to do a Running Sum for dept 1-4??

Thanks so much,
barb

:

If I understand correctly you have deptnum values from 1 to at least 5. You
have grouped by deptnum in your report and want to display a summary of an
expression in the deptnum group footer for deptnum 4 that includes all
previous records.

If this is correct, you could probably use a running sum on a text box and
only display it in the group footer for deptnum 4.

--
Duane Hookom
Microsoft Access MVP


:

I am putting a formula into the
deptnum Footer - it is grouped by deptnum. so the if then I am using is just
Looking at the dept num within that group so the answer of this formula

=Sum(IIf([cost id]<5,[reg pay],0)) and just Sum([reg pay]) is identical -
where do I put it so can see the dept. among All the groups. so the sum of
dept num 4 for ALL of the dept - both formula answers are identical - I want
a GRand total for dept 1t through 4???

I want to show the totals at the end of Dept num 4 AS WELL AS at the end of
the Report - I can put them in the report footer and it works but HOW can I
put this in the end of DEpt 4 and have it sum dept 1 through 4???

thanks,
barb
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top