Payment: CCur(Switch([Grade] = "A", 7, [Grade] = "B", 5,
[Grade] = "C", 3, [Grade] = "D", 1, [Grade] = "F",
0, True, Null))
and technically; he was using the grade 'E' and not the grade F right?
Payment: CCur(Switch([Grade] = "A", 7, [Grade] = "B", 5,
[Grade] = "C", 3, [Grade] = "D", 1, [Grade] = "E",
0, True, Null))
or you could make a function like this; paste this in a new module
Public Function GetGradeDollarValue(strGrade as string) as Currency
Dim cOut as Integer
Select Case strGrade
Case "A"
cOut = 7
Case "B"
cOut = 5
Case "C"
cOut = 3
Case "D"
cOut = 1
Case "E"
cOut = 0
Case Else
cOut = 0
End Select
GradeDollarValue = CCur(cOut)
End Function
And then you could just consume this function in expression builder for
example.
Or; if you were using Access Data Projects-- which are truly the best /
cheapest way to do *anything* then you could juse write a similiar UDF
like this
Create Function dbo.GetGradeDollarValue(
@strGrade as string
)
Returns Money
AS
BEGIN
DECLARE @cOut INTEGER
SET @cOut = Case @strGrade
When "A" Then 7
When "B" Then 5
When "C" Then 3
When "D" Then 1
When "E" Then 0
Else 0
END
RETURN CAST @cOut AS MONEY
End Function
Please forgive me if the syntax is off a little bit; I'm a QBE guy and
I just LOVE the query designer in Access.
The ability to write sprocs and views in design view-- is a feat that
is very very friendly; and it's an enterprise level database system.
For example; you could write the function once and then utilize it
through any function; instead of needing to rewrite it, or copy / paste
it every app you need it.
-Aaron
John said:
I have a database and I want to calculate something on my report. It's a
grade database that pulls on the form a grade from a marking period and I
want each grade to autofill, on the form, a payment for that particular
grade. For example A = $7.00 and B=$5.00, what type of box would I create to
get this result? If you need more information please let me know. Thanks.
Two suggestions:
1. Preferred, as Aaron suggests: create a two-field table with a text
field Grade and a currency field Payment; fill it with one row for
each grade letter. Join it to your Query.
2. Less ideal, as it's harder to maintain: use a Switch() function:
Payment: Switch([Grade] = "A", CCur(7.0), [Grade] = "B", CCur(5.0),
[Grade] = "C", CCur(3.0), [Grade] = "D", CCur(1.0), [Grade] = "F",
CCur(0.), True, Null)
John W. Vinson[MVP]
