@0[Keith-Fiedler] as a non-member, we cannot tell what is your skill level. How well do you know Access, and do you know VBA?

Math in Fields

Sami Shamma: I know quite a bit of Access enough to build simple to a little bit complex databases.  As far as Visual Basic, I took courses as part of a college degree many years ago.  I can manage to work thru VBA coding using 2 Access reference books I have as well as some online help.

@0[Keith-Fiedler]

The attach function converts a number that is currency into English words. We use this function to print checks. You need to write a similar function to write your display fields in the format of "5 3/4" For example.

Details

Function English(ByVal N As Currency) As String

   Const Thousand = 1000@
   Const Million = Thousand * Thousand
   Const Billion = Thousand * Million
   Const Trillion = Thousand * Billion
  
   'If (N = 0@) Then English = "zero": Exit Function
   If (N = 0@) Then English = "": Exit Function
  
   Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
   Dim Frac As Currency: Frac = Abs(N - Fix(N))
   If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
   Dim AtLeastOne As Integer: AtLeastOne = N >= 1
  
   If (N >= Trillion) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " trillion"
      N = N - Int(N / Trillion) * Trillion
      If (N >= 1@) Then Buf = Buf & " "
   End If
  
   If (N >= Billion) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion"
      N = N - Int(N / Billion) * Billion
      If (N >= 1@) Then Buf = Buf & " "
   End If
  
   If (N >= Million) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N \ Million) & " million"
      N = N Mod Million
      If (N >= 1@) Then Buf = Buf & " "
   End If
  
   If (N >= Thousand) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N \ Thousand) & " thousand"
      N = N Mod Thousand
      If (N >= 1@) Then Buf = Buf & " "
   End If
  
   If (N >= 1@) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N)
   End If
  
   If (Frac = 0@) Then
      Buf = Buf & " exactly"
   ElseIf (Int(Frac * 100@) = Frac * 100@) Then
      If AtLeastOne Then Buf = Buf & " and "
      Buf = Buf & Format$(Frac * 100@, "00") & "/100"
   Else
      If AtLeastOne Then Buf = Buf & " and "
      Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"
   End If
  
   English = Buf
End Function
Copy