Solved

adding a calculation on form

Posted on 2013-01-30
8
271 Views
Last Modified: 2013-01-31
hello,
have attached database-
open database and a form comes up- click Item code and name button-
( enter 101 for item code and jim for name)

a Form comes up that displays avg time it takes Jim to work on item code 101-
question:
Is there a way to display also on this form? the total avg time it takes all names listed in the table?
Thank you,
DB101.accdb
0
Comment
Question by:davetough
  • 4
  • 3
8 Comments
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 38836296
On your Form2 you can create a text box control with a Control Source expression of:

=DAvg("[Time Spent]","Table1")
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 38836297
You could use a DSum:

Msgbox DSum("[Time Spent]", "Table1", [Item Code]='101')

Or if the form is bound to that table, and you want too get the Item Code dynamically:

Msgbox DSum("[Time Spent]", "Table1", [Item Code]='" & Me![Item Code] & "'')
0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 400 total points
ID: 38836887
"You could use a DSum"

I thought the Questioner wanted the average of all in Table1? ... but it is unclear about whether or not to filter on the item code.

---

So, if filtering is the desired result, then you can apply the filter LSM created in the DAvg() function:

=DAvg("[Time Spent]","Table1", [Item Code]='" & Me![Item Code] & "'')
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:davetough
ID: 38839230
the first answer worked fine and then I realized I did want to filter on item code-
am attaching error I am getting- can see what am doing wrong- have tried LM solution too and not working-
thank you
error.docx
0
 

Author Comment

by:davetough
ID: 38839555
will post another question-thank you
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 38839642
sorry about the error.  the Me keyword is valid in VBA, however in a Control Source expression, you use [Form]

=DAvg("[Time Spent]","Table1", [Item Code]='" & [Form]![Item Code] & "'')

or, you  just drop the form reference since your scope is the current form ...


=DAvg("[Time Spent]","Table1", [Item Code]='" & [Item Code] & "'')
0
 

Author Comment

by:davetough
ID: 38839730
thanks - you know I am still getting error-
can you insert code into txtbox on form and attach db back here?

also just to confirm I am doing this right -am inserting code into the control source of the unbound textbox on form ( form2)
thanks
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 38842376
This ...

=DAvg("[Time Spent]","Table1", [Item Code]='" & [Form]![Item Code] & "'') 

Open in new window


Should be this ...

=DAvg("[Time Spent]","Table1", "[Item Code]='" & [Form]![Item Code] & "'") 

Open in new window


Pay close attention to the quotes!  It seems that LSM inadvertently did not balance the quotes and I copy pasted from him <dazed> ... So, I think I got it right this time!  {note: I am not in a position to post a sample, so hopefully this will do the trick!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question