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)
, IIf(InStr([nr],",")>0,(Left([nr],(InStr([nr],",")))) & (Mid([nr],(InStr([nr],",")+1),2)),[nr]) AS transf
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
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)
valoare = nota
If nota = 10 Then
f_trunc2zec = CDec(Format(valoare, "#"))
f_trunc2zec = (Format(valoare, "#.00"))