In MS Access, there may be situations, more rarely, when you don’t want to round some numerical values (for example, school averages), but truncate – remove some decimals. Since we don’t have a specific function, we can force the display from numeric value to text value. Although it is possible to use functions directly (as in ex. 1) in a query, report, or form, it might be more advisable to create a VBA function, as in ex. 2.
Example 1 – Desired display via text functions (InStr, Left, Mid)
|
1 2 3 |
SELECT [nr] , IIf(InStr([nr],",")>0,(Left([nr],(InStr([nr],",")))) & (Mid([nr],(InStr([nr],",")+1),2)),[nr]) AS transf FROM tabela |
Using a VBA function makes certain refinements easier; for example, if we want all grades to have two decimals, even if it’s just a whole number, except grade 10.
Example 2 – VBA function
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Function f_trunc2zec(nota As Double) As Variant Dim valoare As String, delim As String delim = "," ' as the case may be, it can be a period or a comma, depending on the Regional Settings If InStr(nota, delim) > 0 Then valoare = Left(nota, (InStr(nota, delim))) & Mid(nota, (InStr(nota, delim) + 1), 2) Else valoare = nota End If If nota = 10 Then f_trunc2zec = CDec(Format(valoare, "#")) Else f_trunc2zec = (Format(valoare, "#.00")) End If End Function |
