novice help: vba lookup function to use in multiple queries (1 Viewer)

[LBayley]

I am completely new to programming and self-taught on access so apologies if I'm asking very naïve questions here.

I have a database with various tables containing information about students, timetabling, assignment submission dates and multiple tables with grades for various assessments. All grades are held as percentages.

In a large number of different queries / reports I want to output the grade as an item from verbose scale with 17 points (excellent first, high first etc.). I've set up a table called 17pointscale which contains fields called 17pointscale (with the verbose names), lowerlimit (number) and upperlimit (number).

I have a query in SQL (which works) to take the percentage grade from one of my grade tables AssessedWorkGrades.Grade and return the text on the 17 point scale.

SELECT AssessedWorkGrades.Grade, [17PointScale].[17PointScale]
FROM AssessedWorkGrades LEFT JOIN 17PointScale ON ([AssessedWorkGrades].[Grade]
>= [17PointScale].[LowerLimit]) AND ([AssessedWorkGrades].[Grade] <= [17PointScale].[UpperLimit]);

Is there any way of converting the SQL to a custom vba function which would enable me to use this as a lookup in a large number of queries.

I think that it should be possible to set up a function called ScaleGrade and in any query Expression: ScaleGrade(XXX) will take XXX and return the 17 point scale.

I think that AssessedWorkGrades.Grade needs to be replaced by a variable that is inputted on use of the function but am not sure how to accomplish this.

Thank you for any help!

 

[Ranman256]

in vbe, create a new module, :menu, INSERT, MODULE.
click inside the new module
menu, INSERT, PROCEDURE
choose FUNCTION option
type : ScaleGrade in the NAME box, OK
create a param argument(s) in the parens...
as many params as you need.
(i use pvXXX, p = param, v = variant, but param names are your choice)
NOTE: be sure to assign your final value to the Function Name

Public Function ScaleGrade(byval pvVal)
dim vNewVal
vNewVal = pvVal * 7     'some calculation

ScaleGrade =  vNewVal      'assign the new value to the function as the return value
end function