Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sum a Control 1

Status
Not open for further replies.
Gaffi1

Gaffi1

Technical User
Apr 23, 2004
70
US
I am creating an employee production tracking database. I've had no problems up until now while making the report. In the report, in the detail section I have the data with two text box' calculating weighted averages. In the group footer, I have a text box to sum the weighted averages. When I try to view the report, it asks for the parameter of the weighted average textbox. If I hit enter I get a blank. If I enter a number, the result is the number of records times the value entered.

Reading through, the closest post I found to this problem was but the person gave up and went to a different program instead of creating a work around.

The weighted average box is absolutly essential for this to work as it's the single value that determines incentives for our employees. If needed, I can provide a link to a dummy version of the database online for someone to review. I really appreciate your help!
 
Sort by date Sort by votes
Alright, I like to think that there are three important types of controls...
- Bound to a field in a table. The value in the table is reflected in the control. A value entered in the control updates the value in the table.
- Unbound controls such as combo boxes and list boxes used to select / filter / manipulate output and such. For example, select all invoices assigned to a customer, order invoices by descending date, find all paid invoices. I just use invoices here, but hopefully you get the gist.
- Calculated values depending values on the form, or from a table, or combination thereof.

Both you and xicana are working with the calculated values. However, xicana had problems in two areas -- an out of scope issue where totals between sections of a report, and not pursuing aggregate functions. (Too bad she gave up - Duane / dhookom is top notch.)

Aggregate functions reference the tables directly which is can be very effective. These functions include DLookup, DSum, etc.

You can also perform this action "behind the scenes" using VBA code. This apporach is actually easier to use once you have worked through a couple of examples.

Your post is in the "Form" forum section and not the "Report" forum which you have already visited, so I will focus on this.

(But I have to wonder about your comment on...
...In the report, in the detail section ... When I try to view the report
Click to expand...
)


To use the aggregate function, use, for example, DSum...
=DSum("[TargetField]", "TableName", "[KeyField] = " & Me.FieldName)

The other functions work pretty much the same way.

For the total of invoice for InvoiceID, assuming InvoiceID is numeric, the syntax would something like...
=DSum("[ExtendedAmount]", "tblInvoiceDetail", "[InvoiceID] = " & Me.InvoiceID)

From the coding level, you would use OnCurrent event property, AfterUpdate property, and perhaps other events. In this case, the amount of fat in meat with three types of measurements for a month...

Code: 
Dim sngMethod1 as Single, sngMethod2 as Single, sngMethod3 as Single
Dim strQ as String

strQ = Chr$(34)
'Note: WHERE clause is complex - two checks, 
'      MethodType is a text string, the Month is numeric
sngMethod1 = DAvg("FatReading", tblMeatQuality", "MethodType = " & strQ & "Method1" & strQ & " and Month(SampleDate) = " & Month(Me.SampleDate)

sngMethod2 = DAvg("FatReading", tblMeatQuality", "MethodType = " & strQ & "Method2" & strQ & " and Month(SampleDate) = " & Month(Me.SampleDate)

sngMethod3 = DAvg("FatReading", tblMeatQuality", "MethodType = " & strQ & "Method3" & strQ & " and Month(SampleDate) = " & Month(Me.SampleDate)

Me.AvgReading = (sngMethod1 + sngMethod2 + sngMethod3)/3

Where Me.AvgReading is a text box on a form.

Two more points....
- You can create a query and reference the query for your total or average
- I feel, by far, the most flexible way to perform complicated calculations from either a form or report is to create your own function. Using the Fat content in meat as an example...

Code: 
Function CalcFat (ReadingMonth as Interger) as Single

Dim sngMethod1 as Single, sngMethod2 as Single, sngMethod3 as Single
Dim strQ as String

strQ = Chr$(34)

sngMethod1 = DAvg("FatReading", tblMeatQuality", "MethodType = " & strQ & "Method1" & strQ & " and Month(SampleDate) = " & ReadingMonth 

sngMethod2 = DAvg("FatReading", tblMeatQuality", "MethodType = " & strQ & "Method2" & strQ & " and Month(SampleDate) = " & ReadingMonth 

sngMethod3 = DAvg("FatReading", tblMeatQuality", "MethodType = " & strQ & "Method3" & strQ & " and Month(SampleDate) = " & ReadingMonth )

CalcFat = (sngMethod1 + sngMethod2 + sngMethod3)/3

End Function

The advantage of using the function is that it can be called from just about anywhere - a control on a form, a report, even a query.

Since you have not provided specifics, I can not provide a specific answer, but hopefully, at least one of these ideas helped.

Richard
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

breezett93
  • Locked
  • Question
Function that find all controls on a form and puts their tab index in numerical order
Replies
19
Views
656
dhookom
dhookom
TomSalvato
  • Locked
  • Question
Getting the Monday date from a week number 1
Replies
2
Views
547
TomSalvato
TomSalvato
Bubba002
  • Locked
  • Question
Pass a subform fieldname-ID as a string variable in CF expression
2 3
Replies
50
Views
2K
dhookom
dhookom
BJZeak
  • Locked
  • Question
Struggling with finding an answer to a simple question
Replies
5
Views
498
BJZeak
BJZeak
MattGreer
  • Locked
  • Question
Add Field Description to a Form 2
Replies
5
Views
296
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top