I am extracting data from external data base into a query. I have a field
for Catergory which pulls numbers 1,2,3. I need to change them to Roman
numbers I, II, III in order to work with my classification.

I can't figure out a way to accomplish that in access 2003.
Can someone help?

That's why the Romans had so much trouble with arithmetic
There is no way to derive the roman numeral values. The best solution is a
cross-reference table. You keep the normal number field in the table for
sorting purposes but use a lookup table to get the RN value for display.

"Waheeda Ali" <(E-Mail Removed)> wrote in message
news:B1498AB9-1B2A-4ED7-8D9D-(E-Mail Removed)...
>I am extracting data from external data base into a query. I have a field
> for Catergory which pulls numbers 1,2,3. I need to change them to Roman
> numbers I, II, III in order to work with my classification.
>
> I can't figure out a way to accomplish that in access 2003.
> Can someone help?
>

On Mon, 7 Jan 2008 11:37:00 -0800, Waheeda Ali
<(E-Mail Removed)> wrote:

>I am extracting data from external data base into a query. I have a field
>for Catergory which pulls numbers 1,2,3. I need to change them to Roman
>numbers I, II, III in order to work with my classification.
>
>I can't figure out a way to accomplish that in access 2003.
>Can someone help?

I found this from long, long ago... see if it works for you.

Public Function Roman(ByVal intIn As Long) As String
Dim Unit As Variant
Dim Five As Variant
Unit = Array("I", "X", "C", "M", "Xbar", "Cbar", "Mbar")
Five = Array("", "V", "D", "L", "Vbar", "Dbar", "Lbar")
Dim iDigit As Integer
If intIn < 0 Then
Roman = "-"
intIn = Abs(intIn)
Else
Roman = ""
End If
If intIn > 4000000 Then
Roman = Roman & "<number too large>"
Exit Function
End If

For iDigit = 6 To 1 Step -1
Do While intIn >= 10 ^ iDigit
Roman = Roman & Unit(iDigit)
intIn = intIn - 10 ^ iDigit
Loop
If intIn >= 9 * 10 ^ (iDigit - 1) Then
Roman = Roman & Unit(iDigit - 1) & Unit(iDigit)
intIn = intIn - 9 * 10 ^ (iDigit - 1)
End If

If intIn >= 5 * 10 ^ (iDigit - 1) Then
Roman = Roman & Five(iDigit)
intIn = intIn - 5 * 10 ^ (iDigit - 1)
End If

If intIn >= 4 * 10 ^ (iDigit - 1) Then
Roman = Roman & Unit(iDigit - 1) & Five(iDigit)
intIn = intIn - 4 * 10 ^ (iDigit - 1)
End If

Do While intIn >= 10 ^ (iDigit - 1)
Roman = Roman & Unit(iDigit - 1)
intIn = intIn - 10 ^ (iDigit - 1)
Loop

Next iDigit
End Function



Sample results:

?roman(13)
XIII
?roman(32768)
XbarXbarXbarMMLCCDXVIII
?roman(3999999)
MbarMbarMbarCbarMbarXbarCbarMXbarCMXCIX


John W. Vinson [MVP]

(...)
>>I am extracting data from external data base into a query. I have a field
>>for Catergory which pulls numbers 1,2,3. I need to change them to Roman
>>numbers I, II, III in order to work with my classification.
>>
>>I can't figure out a way to accomplish that in access 2003.
>>Can someone help?
>
> I found this from long, long ago... see if it works for you.
>
> Public Function Roman(ByVal intIn As Long) As String
> Dim Unit As Variant
> Dim Five As Variant
> Unit = Array("I", "X", "C", "M", "Xbar", "Cbar", "Mbar")
> Five = Array("", "V", "D", "L", "Vbar", "Dbar", "Lbar")
> Dim iDigit As Integer
(...)

Or You can set reference to MSOWCF.DLL library (commonly in C:\Program
Files\Microsoft Office\Office). Following code works then:
Function To_Roman(number) As String
Dim obj As MSOWCFLib.OCFunc
Set obj = New MSOWCFLib.OCFunc
To_Roman = obj.ROMAN(number)
Set obj = Nothing
End Function
(However this method is suitable to small numbers only.)

K.P.
www.access.vis.pl

On Mon, 7 Jan 2008 23:00:10 +0100, "Krzysztof Pozorek [MVP]" <(E-Mail Removed)>
wrote:

>(...)
>>>I am extracting data from external data base into a query. I have a field
>>>for Catergory which pulls numbers 1,2,3. I need to change them to Roman
>>>numbers I, II, III in order to work with my classification.
>>>
>>>I can't figure out a way to accomplish that in access 2003.
>>>Can someone help?
>>
>> I found this from long, long ago... see if it works for you.
>>
>> Public Function Roman(ByVal intIn As Long) As String
>> Dim Unit As Variant
>> Dim Five As Variant
>> Unit = Array("I", "X", "C", "M", "Xbar", "Cbar", "Mbar")
>> Five = Array("", "V", "D", "L", "Vbar", "Dbar", "Lbar")
>> Dim iDigit As Integer
>(...)
>
>Or You can set reference to MSOWCF.DLL library (commonly in C:\Program
>Files\Microsoft Office\Office). Following code works then:
>Function To_Roman(number) As String
> Dim obj As MSOWCFLib.OCFunc
> Set obj = New MSOWCFLib.OCFunc
> To_Roman = obj.ROMAN(number)
> Set obj = Nothing
>End Function

Heh. Interesting - I wasn't aware of this function's existance.

>(However this method is suitable to small numbers only.)

Well, Roman numerals are suitable to small number only as far as that goes.
The "I bar" and "X bar" notations for 10000 and 100000 are authentic but
probably were very rarely used even when Roman numerals were the standard.

Now where's my original Greek scroll of Psammites (the Sand Reckoner)... It's
around here somewhere, I'm sure young Archimedes made me a copy a while
back...

John W. Vinson [MVP]

Hello John

I enjoyed examining your old code re Roman Numerals as a learning exercise.
But, I think the following line...

Five = Array("", "V", "D", "L", "Vbar", "Dbar", "Lbar")

needs to be changed to...

Five = Array("", "V", "L", "D", "Vbar", "Lbar", "Dbar")

regards,
cinnie


--
cinnie


"John W. Vinson" wrote:

> On Mon, 7 Jan 2008 11:37:00 -0800, Waheeda Ali
> <(E-Mail Removed)> wrote:
>
> >I am extracting data from external data base into a query. I have a field
> >for Catergory which pulls numbers 1,2,3. I need to change them to Roman
> >numbers I, II, III in order to work with my classification.
> >
> >I can't figure out a way to accomplish that in access 2003.
> >Can someone help?
>
> I found this from long, long ago... see if it works for you.
>
> Public Function Roman(ByVal intIn As Long) As String
> Dim Unit As Variant
> Dim Five As Variant
> Unit = Array("I", "X", "C", "M", "Xbar", "Cbar", "Mbar")
> Five = Array("", "V", "D", "L", "Vbar", "Dbar", "Lbar")
> Dim iDigit As Integer
> If intIn < 0 Then
> Roman = "-"
> intIn = Abs(intIn)
> Else
> Roman = ""
> End If
> If intIn > 4000000 Then
> Roman = Roman & "<number too large>"
> Exit Function
> End If
>
> For iDigit = 6 To 1 Step -1
> Do While intIn >= 10 ^ iDigit
> Roman = Roman & Unit(iDigit)
> intIn = intIn - 10 ^ iDigit
> Loop
> If intIn >= 9 * 10 ^ (iDigit - 1) Then
> Roman = Roman & Unit(iDigit - 1) & Unit(iDigit)
> intIn = intIn - 9 * 10 ^ (iDigit - 1)
> End If
>
> If intIn >= 5 * 10 ^ (iDigit - 1) Then
> Roman = Roman & Five(iDigit)
> intIn = intIn - 5 * 10 ^ (iDigit - 1)
> End If
>
> If intIn >= 4 * 10 ^ (iDigit - 1) Then
> Roman = Roman & Unit(iDigit - 1) & Five(iDigit)
> intIn = intIn - 4 * 10 ^ (iDigit - 1)
> End If
>
> Do While intIn >= 10 ^ (iDigit - 1)
> Roman = Roman & Unit(iDigit - 1)
> intIn = intIn - 10 ^ (iDigit - 1)
> Loop
>
> Next iDigit
> End Function
>
>
>
> Sample results:
>
> ?roman(13)
> XIII
> ?roman(32768)
> XbarXbarXbarMMLCCDXVIII
> ?roman(3999999)
> MbarMbarMbarCbarMbarXbarCbarMXbarCMXCIX
>
>
> John W. Vinson [MVP]
>