The other day, I was asked by a client how to show Roman Numerals in an Access database. They're a bit tricky, because they follow a strange pattern. However, there are rules, and if there are rules then Access can do it!
Here's the code I used. Please feel free to use it, but please include the credit comment at the top! Just copy and paste this into a Module in your database.
---- copy ----
Public Function DecimalToRoman(lngDecimal As Long) As String
' Pass this function an integer value and it will return
' a string containing the Roman Numeral equivalent.
' Author : J Kane, Tarragon Consultancy Ltd
' http://www.tarragonconsultancy.co.uk
Dim strNumeral(0 To 12) As String
Dim intLoop As Integer
Dim lngCurrentMilestone As Long
' Define the numerals and their decimal equivalents
strNumeral(0) = "I 1"
strNumeral(1) = "IV4"
strNumeral(2) = "V 5"
strNumeral(3) = "IX9"
strNumeral(4) = "X 10"
strNumeral(5) = "XL40"
strNumeral(6) = "L 50"
strNumeral(7) = "XC90"
strNumeral(8) = "C 100"
strNumeral(9) = "CD400"
strNumeral(10) = "D 500"
strNumeral(11) = "CM900"
strNumeral(12) = "M 1000"
If lngDecimal = 0 Then
' No Roman Zero - return a null string!
DecimalToRoman = ""
Else
' Try each Roman Numeral in turn, starting with the largest
For intLoop = 12 To 0 Step -1
lngCurrentMilestone = Val(Mid(strNumeral(intLoop), 3))
' Loop around, building the result string
Do While lngDecimal >= lngCurrentMilestone
DecimalToRoman = DecimalToRoman & "" & Trim(Left(strNumeral(intLoop), 2))
lngDecimal = lngDecimal - lngCurrentMilestone
Loop
Next intLoop
End If
End Function
---- end copy ----
To call the function just use something like
<mystring> = DecimalToRoman(104)
Which would result in <mystring> containing "CIV"
Enjoy!
Jeff Kane
