Avg!!!

[Guest]

I have a form that's used to disply the search citeria,the form looks like
this :
Name Job description phone Mobile Language Marks
John employee 00 00 Italian 60
Tony Account manager 00 00 French 90
Peter Supervisor 00 00 Spanish 100

What should i write in the control source of an unbound text box to find the
Avg of the [Marks] field for the "French" language ,for example.

Regards

 

[Guest]

You can usethe DAvg() function.
=DAvg("Marks","MyTable","[Language] = 'French'")

Look in Access help for more information on this function.

Barry

 

[Guest]

Thanks alot Barry,
But i don't want to refer to table,
I want to refer to the current for,because as i told you it's a searching form

You can usethe DAvg() function.
=DAvg("Marks","MyTable","[Language] = 'French'")

Look in Access help for more information on this function.

Barry

I have a form that's used to disply the search citeria,the form looks like
this :
Name Job description phone Mobile Language Marks
John employee 00 00 Italian 60
Tony Account manager 00 00 French 90
Peter Supervisor 00 00 Spanish 100

What should i write in the control source of an unbound text box to find the
Avg of the [Marks] field for the "French" language ,for example.

Regards

 

[Svetlana]

=DAvg("Marks","MyTable","[Language] =""" & [NameOfLanguageControl] &
"""")

 

[Guest]

I think you got me wrong,
I don't want to refer to table at all!!
I'd like to find the average of [Marks] based on the searching reults that
appear on the form regardless the table,as i may delimit my search within a
certain period of time.
IT MAY BE SOMETHING LIKE THIS :
=Abs(Avg([marks],[language]="french"))
i tried it but it does not work

 

[Dirk Goldgar]

I have a form that's used to disply the search citeria,the form looks
like this :
Name Job description phone Mobile Language Marks
John employee 00 00 Italian
60 Tony Account manager 00 00 French
90
Peter Supervisor 00 00 Spanish
100

What should i write in the control source of an unbound text box to
find the Avg of the [Marks] field for the "French" language ,for
example.

Regards

You might try

=Sum(IIf([Language]="French", [Marks],
0))/Sum(IIf[Language]="French", 1, 0))

That line will probably have been broken oddly by the newsreader, so
I'll rewrite it on multiple lines for clarity:

=Sum(IIf([Language]="French", [Marks], 0)) /
Sum(IIf[Language]="French", 1, 0))

 

[Douglas J. Steele]

Like it or not, your data is in the table, therefore you should be referring
to the table to work with the data.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I think you got me wrong,
I don't want to refer to table at all!!
I'd like to find the average of [Marks] based on the searching reults that
appear on the form regardless the table,as i may delimit my search within
a
certain period of time.
IT MAY BE SOMETHING LIKE THIS :
=Abs(Avg([marks],[language]="french"))
i tried it but it does not work

=DAvg("Marks","MyTable","[Language] =""" & [NameOfLanguageControl] &
"""")

 

[John Welch]

It seems that what the poster is asking is how to find the average of the
"marks" with the domain being the form's current recordset, and also subject
to the added criterion of "language = something".
A couple of ideas come to mind:
If your search form is based on a named query (that isn't a parameter
query), then you can say:
=DAvg("marks","theSavedQueryName","language="""& [nameOLanguageControl ...
(like Svetlana said)
and then you will get the average for only records that are part of the
current search.

Or -- you could dump your search results into a temporary table and then do
the DAvg on that table.

Or -- you could calculate the average in vba, if you know how to program in
vba.
Here's some basic code that should do the trick. Put it in some event that
happens right after the search. If you do the search in vba, then put this
after that code.

Dim rst As DAO.Recordset
Dim dblSum As Double
Dim dblcount As Double

Set rst = Me.RecordsetClone
rst.MoveFirst
Do While Not rst.EOF
If rst.Fields("language") = "french" Then
(or If rst.Fields("language") = Me.NameOfLanguageControl Then)
dblSum = dblSum + rst.Fields("marks")
dblcount = dblcount + 1
End If
rst.MoveNext
Loop
MsgBox dblSum / dblcount
set rst=nothing

hope this helps
-John

I think you got me wrong,
I don't want to refer to table at all!!
I'd like to find the average of [Marks] based on the searching reults that
appear on the form regardless the table,as i may delimit my search within
a
certain period of time.
IT MAY BE SOMETHING LIKE THIS :
=Abs(Avg([marks],[language]="french"))
i tried it but it does not work

=DAvg("Marks","MyTable","[Language] =""" & [NameOfLanguageControl] &
"""")