I have a report that I would like to have the page numbering style set to use roman numerals. The numbering is working but I have no clue how to format this to work. Hope someone can help me here. Thanks!
I have a report that I would like to have the page numbering style set to use roman numerals. The numbering is working but I have no clue how to format this to work. Hope someone can help me here. Thanks!
here is an example
https://access-programmers.co.uk/for...d.php?t=239009
Thanks, Ajax, but I tried the thread and got totally lost. It's just above my head for me being so new at this to follow along and put things in the correct locations. So much of what I've learned has been tons of trial and error. I'm far from being a programmer. I appreciate the help.
Open VBA Editor and create a general module or use an existing one. Copy/paste the Roman function into module. Remove the "Private" qualifier.
Function can be called from anywhere in database. For instance, in a query:
SELECT *, Roman([fieldname]) AS RN FROM tablename;
or in a textbox:
=Roman([fieldname])
Do this test in VBA Editor immediate window:
?Roman(32)
That will return:
XXXII
So in textbox that has the [Page] element:
=Roman([Page])
Since it only handles numbers up to 3999, I hope that will meet your needs.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thank you so very much, June7! It took me a bit to get it figured out, but I was successful. The first code I put in just wasn't working so I inserted the shorter method of coding and referenced that function of the module within a textbox. Low and behold, there were my Roman numerals and they were perfect! I did edit the function from Roman to UpperRoman and then created one to give me lowercase Roman numerals as well. Now, I have a choice between using either one just with a simple entry. I could have never figured this out without your help and guidance. I just can't thank you enough. I'm using it for a TOC page and only wish it could paginate in the correct place and not have to be printed separately. In my full report, I have first my cover page and then my grouping/sorting section which has all the entries for the actual report and at the end of the report is a thank you page to everyone who submitted recipes for the family recipe book. Since I can't paginate by inserting a sub report into the report heading, I have no choice but to add the thank you report to the end, but that's better than not having it work. It works perfectly in the report footer. Here's the code I entered for both Roman numerals functions within the module:
Public Function LowerRoman(ByVal aValue As Long) As String
Dim i&
Dim varArabic As Variant, varRoman As Variant
Dim strResult As String
varArabic = Array(1, 4, 5, 9, 10, 40, 50, 90, 100, 400, 500, 900, 1000)
varRoman = Array("i", "iv", "v", "ix", "x", "xl", "l", "xc", "c", "cd", "d", "cm", "m")
For i = UBound(varArabic) To LBound(varArabic) Step -1
Do While aValue >= varArabic(i)
aValue = aValue - varArabic(i)
strResult = strResult & varRoman(i)
Loop
Next i
LowerRoman = strResult
End Function
Public Function UpperRoman(ByVal aValue As Long) As String
Dim i&
Dim varArabic As Variant, varRoman As Variant
Dim strResult As String
varArabic = Array(1, 4, 5, 9, 10, 40, 50, 90, 100, 400, 500, 900, 1000)
varRoman = Array("I", "IV", "V", "IX", "X", "XL", "L", "XC", "C", "CD", "D", "CM", "M")
For i = UBound(varArabic) To LBound(varArabic) Step -1
Do While aValue >= varArabic(i)
aValue = aValue - varArabic(i)
strResult = strResult & varRoman(i)
Loop
Next i
UpperRoman = strResult
End Function