Format function for 2 numbers after the decimal/Account for nulls (1 Viewer)

[Serendipityww]

This question is separate from previous questions. I am intensely trying to finish up a database and have lots of questions as I have left everything I can't do to the end.
How can I make the following function accommodate courses with anywhere from 3 to 19 tests? I did this one for a course that has 5 tests and started to make a form for the next course and realized that it has 19 tests and that I could probably write one function for all courses if I could figure out what to do with the courses that have less than 19 tests. If the course has only five test, it will only have five controls on the form for entering test grades. Should I put all 19 and just leave some of them blank?
Also, how do I format this function to have two places after the decimal so that whole numbers and decimal numbers line up? I tried the Help illustrations but couldn't get it to work. I appreciate anyone who can help.

Public Function AveragePercentOfFiveTestsTimesPercent(test1, test2, test3, test4, test5, percent) As Double
AveragePercentOfFiveTestsTimesPercent = (test1 + test2 + test3 + test4 + test5) * percent / 5
End Function

 

[pcs]

in answer to the second (format) question, you don't need to format it in the function. format it in the control that you feed the function to.

think you may have a design issue, as well. i would have 3 tables, courses,students and grades. if you do not, and don't want to re-design, you probably need to use an array.

this link might get you started:
http://www.access-programmers.co.uk/ubb/Forum7/HTML/001782.html

(be especially aware of the NZ() usage, as nulls propagate thru an expression.)

hth,
al

 

[Serendipityww]

I do have those tables. Does that make it a simple issue?
Thanks

 

[pcs]

doesn't make it simple, makes it different!

you can probably do it with queries...check out help on TopValues, Ave(), and domain aggregates.

and, yes i think you would need all 19 of your fields...if in the course table you added a field for number of tests, you could disable the extra fields on your input form. this also would provide the number of tests to get an average, etc.
you can save yourself some extra work downstream by setting the default value for all tests to zero in your table. then you won't need to mess with a bunch of NZ()'s.

hth,
al

 

[Rich]

Surely al only your original approach is correct. Table Grades contains the student Id as a foreign key, the key field from the courses which is a lookup table and the result of the grade it then becomes easier to simply count the number of exams/tests taken for each student.

 

[pcs]

rich, if you are suggesting a VBA array approach, you are probably right.
especially in view of Serendipityww's other post...'Trying to create a function to calculate a grade'.

i'm a 'query kind of guy' and sometimes i push it a bit too far and create complicated queries that would be better done in code.

al

(i still would add the number of tests field, tho. just to make limiting the input fields easier...)




[This message has been edited by pcs (edited 08-19-2001).]