Using Average() on form

[trackmanpete]

To those stuck at home instead of being out in Las Vegas:

I am able to get the average temperature for each month when doing a report by using the formula

avg_Temp_for_Month = average(day->Temp,grp->Month)

.

I created a group "Month" on the report to accomplish this.

However, when I try to use the same formula on a form, I get an error message saying the formula can't find GRP->Month.

My daily temps are listed in the parent table, along side each day of the week.

Is there a way to post the average monthly temperature on a form? I have been able to post the average temperature for the entire list of days on the form.
Would I have to do a filter for each month to get a correct reading? I was hoping that I could generate an average for the month each time I add a new day and new temperature within a specific month. In other words, when I am on a record for a day in May, I would get the average for May. When I am on a record for April, I would get the average temps for April.

The temp field is numeric, as is the month field.
In my sample, go to "Workoutform" and you will see ave temp in upper right hand corner. However, this figure is not just for May, but for all entries in the table.

Thanks,

[Tim Kiebert]

Re: Using Average() on form

To those stuck at home instead of being out in Las Vegas:

Yeh, boo hoo, :(.

Average seems to be geared to the report arena. You will find it in the section of report functions. Try Tableavg() with a filter or dbavg() together with an index.

Edit: I didn't look at your sample yet but if you still don't have success I will.

[trackmanpete]

Re: Using Average() on form

Hi Tim:
I tried the following:

ave_month_temp = tableavg("day.dbf","Month=3","Temp")

day is my table
Month is the month I want the average for
Temp is the field with the daily temps.

This works, but is a fixed month figure. Now I need to figure out how to change the "Month=" to the month for any particular record. I am going to see if I can play around with a variable for the month.

Thanks

[Tim Kiebert]

Re: Using Average() on form

This seems to work. I changed all the may temps to 100 and averaged 100.

ave_month_temp = tableavg("day.dbf","Month="+s_quote(day->Month),"Temp")

[trackmanpete]

Re: Using Average() on form

Tim:
I will try that. Here is what I tried, but with no success:

ave_month_temp = tableavg("day.dbf","Month=Month(Workoutday)","Temp")

where Month(Workoutday) changes as I go through the records and change month to month. I am not familiar with the +s_quote function, so I will do some research on that.

Thanks again for your quick response on a Friday night, no less.

Update: Your code certainly works, now to learn how you did that. This is very nice, thanks.

[trackmanpete]

Re: Using Average() on form

Tim:
Well, that is a very nice addition to my bank of knowledge. Thank you so much. This is another one of those xbasic codes that I don't think I could have discovered on my own.

[Stan Mathews]

Re: Using Average() on form

Here is what I tried, but with no success:

ave_month_temp = tableavg("day.dbf","Month=Month(Workoutday)","Temp")

Tim has qiven you the answer, now to, hopefully, explain the reason your expression didn't work.

The filter in your tableavg() function reads
"Month=Month(Workoutday)"
literally interpreted by Alpha as it reads with no substitution of the result of the expression Month(Workoutday). To make that substitution happen you have to break out of the quotes and concatenate the result, properly formatted to continue the character string.

There's a nice discussion of the process here.

[trackmanpete]

Re: Using Average() on form

Stan:
I read both items in the help files. It must be my non-technical background, as I really have difficulty following the examples provided. I know it is my own lack of having a logical mind. I appreciate your explanation. My solution usually is to try and remember and store these helpful hints. Calculus presented the same kind of challenge to me back in college.

Trying to get an average temperature to show up on a form was a good learning exercise for me. I continue to be fascinated by how good all of you are.

Thanks again.