Using Sum on Un-Bound Fields 2

[romh]

Okay. Here is the deal.
I have a report which has calculates a quantity and then sums it up. I use the Sum() to calculate totals on bound fields. The Sum function however, does not work on unbound fields. How can I do this?

Thanks

 

[jsteph]

you could try the RunningSum property of the textbox, then choose OverGroup or OverAll.
--jsteph

 

[romh]

thanks alot. I'll try it

 

[dhookom]

romh,
If your calculated expression is something like:
=[Qty]*[UnitPrice]
you can calculate the sum
=Sum([Qty]*[UnitPrice])


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[romh]

Yes. But that's exactly the problem. The calculated expression is not as simple as an expression. Its a function call. That function has a couple of if statements. How can I get a sum of those conditions?

Thanks

 

[dhookom]

Considering the limited amount of information you have provided, you might be able to use:
=Sum(YourFunctionCall(...))
If that doesn't work, then either provide more information or use the running sum.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[romh]

I have a text box in the detail section of the report that calls function Over1(). In other words, the control source of that text box is Over1().

Over1 does the following

Private Function Over1()
If (Me![Total Hours] <> 0) Then
If (Me![TotalDay] > 8) Then
Over1 = RoundNew(((Me!TotalDay - 8) * 1.5) + 8, 2)
Else
Over1 = Me![TotalDay]
End If
End If
End Function


In the group footer, I would like to sum the values of Over1(). It seems that Access does not allow to use the Sum function on unbound text boxes? Is there another way of doing this by not using the running sum property?

Thanks again

 

[dhookom]

Your function works on two values: TotalHours and TotalDay. I would rewrite the function to accept the two values as a parameter and then add the function to a module
Public Function Over1(pdblTotHrs as Double, pdblTotDay as Double) as Double
If (pdblTotHrs <> 0) Then
If (pdblTotDay > 8) Then
Over1 = RoundNew(((pdblTotDay - 8) * 1.5) + 8, 2)
Else
Over1 = pdblTotDay
End If
End If
End Function

Then set the control source in the detail to
=Over1([Total Hours],[TotalDay])
In your report footer, use
=Sum(Over1([Total Hours],[TotalDay]))


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[romh]

Great reply. I never considered writing it as a function that took parameters. I haven;t tried it yet but I will. I'll notify you of the conclusion.

Thanks alot

 

[dhookom]

It looks a bit like business rules/logic so it should be maintained in a single module.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[romh]

Do you think you could elaborate more on why some pieces of code should go in separate modules rather than behind the form or report. One more thing, on the module point....
Let's say that I have a module that needs to be updated every month or so on another user's database / computer. How can I give a user a file that will update only that module in his database. Wait, but then again, if the user has an .mde file, there is no way of updating any code. Well anyway, I would appreciate comments.

Rom

 

[dhookom]

The calculation you described seemed less attached to a &quot;report&quot; and more attached to the &quot;data&quot;. When you find yourself creating calculations, consider placing them someplace that you can get at them from any form, report, query, or code.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jsteph]

romh,
There are numerous schemes for updating objects in databases, pushing with Export from a 'mothership', pulling with Import from each client, or using Delete/CopyObject, etc. etc.

With Modules, you can run into problems if you have globals declared in the module being deleted/replaced if you're 'pulling', ie doing from the actual target (client) .mdb, so try to put those in a separate module that won't be touched often.

But job one is to get away from the .mde kludge. Read one of the faq's on security here and use Access Security instead. Yes, it's more work upfront (complaints about complexity (read: lazyness) of Access security is one reason MS foisted the .mde upon us), but like most things that are more work upfront, using Access Security is better in the long run, especially if you need to bring in a new report to 100 client machines, it's alot faster to import those single objects than to recompile and redeploy 100 .mde's.
--jsteph

 

[dhookom]

I would create a method that would replace the entire MDE. The other issue is that I see 5 hard-coded values in your code. If you feel you need to update code to change these values then you should be reading the values from tables.

At all times, you should consider updating records rather than code, forms, reports,...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[romh]

Both you guys are right. Thanks alot for the info. The reason that I use an .mde is because I do not want the user's of my database having the least bit of chance of getting to my code.
An mde removes all code. Updating a form or module would be much harder I guess. But I like the idea of coding numbers in tables instead of in code - as mentioned by Duane.

Thanks jsteph and duane.