If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Calculating averages
I put the formula in the reg footer section. When i run the report it asks
for Total Miles / Total Ltrs in a parameter box. Do you know why? I have also tried using the txtbox control name but it does the same thing. Cheers "Marshall Barton" wrote: TinMan wrote: Can you help me with a problem. I have a calculated field at query level. It is KPL: Total Miles/Total Fuel Used. I am trying to get an average for each driver in a report based on the query. But it asks for KPL whenever i run the report and then obviously returns a blank field with no errors reported. I have tried going back to the query and click on average in the totals but it asks me for KPL when i run the query. What am i doing wrong? Instead of using Avg(KPL), try: Avg([Total Miles] / [Total Fuel Used]) -- Marsh MVP [MS Access] . |
#12
|
|||
|
|||
Calculating averages
Please use Copy/Paste when posting a problem expression.
If you are being prompted to enter something, then that something is not a field in the report's record source query. Maybe you have an extra space, have the [ ] in the wrong place or maybe you just misspelled the something. If that's the expression you want to average, I would expect the report text box's expression to be: =Avg([Total Miles] / [Total Ltrs]) -- Marsh MVP [MS Access] TinMan wrote: I put the formula in the reg footer section. When i run the report it asks for Total Miles / Total Ltrs in a parameter box. Do you know why? I have also tried using the txtbox control name but it does the same thing. Cheers "Marshall Barton" wrote: Instead of using Avg(KPL), try: Avg([Total Miles] / [Total Fuel Used]) TinMan wrote: Can you help me with a problem. I have a calculated field at query level. It is KPL: Total Miles/Total Fuel Used. I am trying to get an average for each driver in a report based on the query. But it asks for KPL whenever i run the report and then obviously returns a blank field with no errors reported. I have tried going back to the query and click on average in the totals but it asks me for KPL when i run the query. What am i doing wrong? |
#13
|
|||
|
|||
Calculating averages
Please use Copy/Paste when posting a problem expression.
If you are being prompted to enter something, then that something is not a field in the report's record source query. Maybe you have an extra space, have the [ ] in the wrong place or maybe you just misspelled the something. If that's the expression you want to average, I would expect the report text box's expression to be: =Avg([Total Miles] / [Total Ltrs]) -- Marsh MVP [MS Access] TinMan wrote: I put the formula in the reg footer section. When i run the report it asks for Total Miles / Total Ltrs in a parameter box. Do you know why? I have also tried using the txtbox control name but it does the same thing. Cheers "Marshall Barton" wrote: Instead of using Avg(KPL), try: Avg([Total Miles] / [Total Fuel Used]) TinMan wrote: Can you help me with a problem. I have a calculated field at query level. It is KPL: Total Miles/Total Fuel Used. I am trying to get an average for each driver in a report based on the query. But it asks for KPL whenever i run the report and then obviously returns a blank field with no errors reported. I have tried going back to the query and click on average in the totals but it asks me for KPL when i run the query. What am i doing wrong? |
#14
|
|||
|
|||
Calculating averages
Please use Copy/Paste when posting a problem expression.
If you are being prompted to enter something, then that something is not a field in the report's record source query. Maybe you have an extra space, have the [ ] in the wrong place or maybe you just misspelled the something. If that's the expression you want to average, I would expect the report text box's expression to be: =Avg([Total Miles] / [Total Ltrs]) -- Marsh MVP [MS Access] TinMan wrote: I put the formula in the reg footer section. When i run the report it asks for Total Miles / Total Ltrs in a parameter box. Do you know why? I have also tried using the txtbox control name but it does the same thing. Cheers "Marshall Barton" wrote: Instead of using Avg(KPL), try: Avg([Total Miles] / [Total Fuel Used]) TinMan wrote: Can you help me with a problem. I have a calculated field at query level. It is KPL: Total Miles/Total Fuel Used. I am trying to get an average for each driver in a report based on the query. But it asks for KPL whenever i run the report and then obviously returns a blank field with no errors reported. I have tried going back to the query and click on average in the totals but it asks me for KPL when i run the query. What am i doing wrong? |
#15
|
|||
|
|||
Calculating averages
Sorry i forgot to post my expression though it the same as yours
=Avg([Total Miles] / [Total Ltrs]). Though, like i say, it throws up the parameter dialog box asking for a value for [Total Miles] then [Total Ltrs] or LGP if i put =Avg([LPG]). Both fields are derived from calculated fields in my query but each have the record source from the query so dont undertand why it asking for values when they there. I have created a footer but i dont get an #error or #Name just a blank text box. Can you help me figure it out Dale???. Incidently, this not the 1st time you help me. I needed a parameter date query to find conflicting holiday requests by staff few months ago and you got it spot on. I Thanked you but got no reply. But Thanx again. It actually pretty good!! "Marshall Barton" wrote: Please use Copy/Paste when posting a problem expression. If you are being prompted to enter something, then that something is not a field in the report's record source query. Maybe you have an extra space, have the [ ] in the wrong place or maybe you just misspelled the something. If that's the expression you want to average, I would expect the report text box's expression to be: =Avg([Total Miles] / [Total Ltrs]) -- Marsh MVP [MS Access] TinMan wrote: I put the formula in the reg footer section. When i run the report it asks for Total Miles / Total Ltrs in a parameter box. Do you know why? I have also tried using the txtbox control name but it does the same thing. Cheers "Marshall Barton" wrote: Instead of using Avg(KPL), try: Avg([Total Miles] / [Total Fuel Used]) TinMan wrote: Can you help me with a problem. I have a calculated field at query level. It is KPL: Total Miles/Total Fuel Used. I am trying to get an average for each driver in a report based on the query. But it asks for KPL whenever i run the report and then obviously returns a blank field with no errors reported. I have tried going back to the query and click on average in the totals but it asks me for KPL when i run the query. What am i doing wrong? . |
#16
|
|||
|
|||
Calculating averages
TinMan wrote:
Sorry i forgot to post my expression though it the same as yours =Avg([Total Miles] / [Total Ltrs]). Though, like i say, it throws up the parameter dialog box asking for a value for [Total Miles] then [Total Ltrs] or LGP if i put =Avg([LPG]). Both fields are derived from calculated fields in my query but each have the record source from the query so dont undertand why it asking for values when they there. I have created a footer but i dont get an #error or #Name just a blank text box. Can you help me figure it out Dale???. Incidently, this not the 1st time you help me. I needed a parameter date query to find conflicting holiday requests by staff few months ago and you got it spot on. I Thanked you but got no reply. But Thanx again. It actually pretty good!! When you post a simple thanks to a post, it means that you have solved your problem and no further discussion is needed. A you're welcome reply does not add any value to the thread and is considered a waste of bandwidth. To your real problem. The aggregate functions (Count, Sum, Avg, etc) are unaware of controls on a form/report (and calculated fields in the same query) They ony know about fields in the form/report's record source table/query. In other words, you can not use things "derived from calculated fields in my query", they must actually be straight from the query. Use the expressions that were used to derive the values. -- Marsh MVP [MS Access] |
#17
|
|||
|
|||
Calculating averages
Thank you for replying again, I take your point on bandwidth does that mean i
cant say thanks. How can i source the original values if my form is based on a query would i need to use a dlookup. Sorry about the Dale bit he helping with another problem. One last bit of waste bandwidth. How do i keep track of my posts. I have asked microsoft but they not help. I find a post then click my name and seach for recent posts but not ideal. Thanz "Marshall Barton" wrote: TinMan wrote: Sorry i forgot to post my expression though it the same as yours =Avg([Total Miles] / [Total Ltrs]). Though, like i say, it throws up the parameter dialog box asking for a value for [Total Miles] then [Total Ltrs] or LGP if i put =Avg([LPG]). Both fields are derived from calculated fields in my query but each have the record source from the query so dont undertand why it asking for values when they there. I have created a footer but i dont get an #error or #Name just a blank text box. Can you help me figure it out Dale???. Incidently, this not the 1st time you help me. I needed a parameter date query to find conflicting holiday requests by staff few months ago and you got it spot on. I Thanked you but got no reply. But Thanx again. It actually pretty good!! When you post a simple thanks to a post, it means that you have solved your problem and no further discussion is needed. A you're welcome reply does not add any value to the thread and is considered a waste of bandwidth. To your real problem. The aggregate functions (Count, Sum, Avg, etc) are unaware of controls on a form/report (and calculated fields in the same query) They ony know about fields in the form/report's record source table/query. In other words, you can not use things "derived from calculated fields in my query", they must actually be straight from the query. Use the expressions that were used to derive the values. -- Marsh MVP [MS Access] . |
|
Thread Tools | |
Display Modes | |
|
|