I have one lone problem in finishin 1

[CMooreJr]

I have one lone problem in finishing up a very comples report. Here is the scenerio:

1) There is a complexity field which can be 1, 2, 3, or 4. Depending on the value chosen in the form, field just pulls over the value. The field name is Project_complexity.

2) There is an Hours field on the report. The hours will depend on the complexity. if it is complesity 1, it is 3 hours, complexity 2 is 4 hours, 3 is 6 hours, and 4 is 10 hours. Using the Choose() function, I have this working great. ("=Choose([project_complexity],3,4,6,10)

3) I have a row of fields that compares the "in_store_date to the current field date. There are dates by week for 4 months) if it is less, it puts the hours in that field using...
=IIf([Week1]<[in_store_date],Choose([project_complexity],3,4,6,10),&quot;0&quot

4) WHAT I NEED IS THIS....I need s SUM of the [Week1] field at the bottom of the report. Nothing I try seems to work. do you experts have a solution? (example of report below...

complexity in store date 4/1/03 4/8/03 4/15/03
1 4/7/03 3
4 4/17/03 10 10 10
2 4/9/03 4 4

**NEED SUM of ROWS HERE!**

thanks!

 

[CosmoKramer]

In the report footer line up text boxes underneath each of the weeks and set as their control source something like this:

=Sum([hours])

* replace [hours] with whatever you have named those controls.....

 

[CMooreJr]

Hey Cosmo...there inlies my problem...the control source for those boxes is a formula to determine if a number appears tehre or not, and if so, which number. The formula is

=IIf([Week1]<[in_store_date],Choose([project_complexity],3,4,6,10),&quot;0&quot

so I couldn't get SUM() to work without an error.

 

[AndyMan5]

Could you possibly be summing a text field with &quot;0&quot;
in: =IIf([Week1]<[in_store_date],Choose([project_complexity],3,4,6,10),&quot;0&quot ??

You might be better off making the Control Source in your total fields be: =Sum(IIf([Week1]<[in_store_date],Choose([project_complexity],3,4,6,10),0))

I'm assuming this is not a Crosstab query.

 

[CosmoKramer]

OK, I think I'm finally paying attention now.....

See if this helps:

Add an invisible text box next to each displayable text box. You can make them as small as possible. Set the control source of each invisible control to the NAME of the corresponding visible one. Also, set the Running Sum property for each invisible control to Over All.

In your report footer add an unbound text box under each column and define the control source for each to be its invisible text box, which at the end of the report will hold the summed value....

Let me know how you make out....

 

[CMooreJr]

PERFECT COSMO!!!! While I've been using access for a while now, I never knew how to get around using the NAME as a Control Source. I knew there was a way, just couldnt get it to work!!

Thank you so much! My database is complete! (well, after I subtract the running sum from a number....

Thank you too AndyMan for catching the quotes...your formula in the sum box however, caused a &quot;to complex&quot; error message....