Solved: Changing Page Number Style

[eed]

Hi, all,

I'm wondering if there's a simple way (or a difficult way!) to get Roman numeral page numbers in a report (i.e., "i, ii, iii, iv, v, ...")

I've seen expressions to change the page number format, such as =Format([Page], "000") to get 001, 002, etc. But I am unsure how to approach the issue for a non-"Arabic"-numeral style.

Thanks for any suggestions!

~ eed

[OBP]

Sorry I do not know, but I would imagine the only way to do it would be to set up a translation table that would convert the "Values" to Roman Numeral "text".

[chocobochick]

At least in my copy of Office 2000, Access lists the ROMAN worksheet function in its help pages but does not seem to actually support it (I had the same problem with NETWORKDAYS). However, it still seems to work in Excel.

This link (http://www.applecore99.com/gen/gen003.asp) gives an example on how to make a Visual Basic "wrapper" function that uses Excel's worksheet function to generate Roman numerals. If you place a function like this in your form's code, you can probably access it from the form by binding a textbox to the expression =NameOfWrapperFunction([Page]).

Then again, you probably don't want to be starting an instance of Excel everytime an Access report needs to generate a single page number. You may need to write your own version of this function from scratch. :wot

[xCav8r]

I would recommend merging your data with a Word template. You have a lot better control over things like this going that route.

[eed]

I would recommend merging your data with a Word template. You have a lot better control over things like this going that route.
xCav8r, that is an option that has occurred to me, but my past experiences with automated mail merge from Access to Word have been sketchy. One second they work beautifully, and the next, they blow up. I am creating a very long and complex report. But, so far, I have been able to successfully control through Access all the necessary details of table gridlines, page breaks, page number formatting, etc. The Roman numeral page numbers in the introductory section have been my only sticking point so far, so I'm trying not to resort to a mail merge solution yet if I can help it.

Chocobochick, I will check out the Excel wrapper you linked and see if I can incorporate it (or some adaptation of it) in my application.

I might just set up a table of equivalent values... There are never more than ten pages that need to be numbered with Roman numerals. In such finite and controlled circumstances, I may be able to get away with a solution like this, although it won't scale very well if the number of pages in the section ever increases in the future. Ideally I can work with some variation of that ROMAN worksheet function.

Thanks, all, for the great comments! I'm going to play with it this week, and I will post again when I settle on a viable answer for my application.

~ eed

[Norie]

Set a reference to the Microsoft Excel x.x Object Library and try this.


Function MyRoman(x) As String
MyRoman = WorksheetFunction.ROMAN(x)
End Function


At least in my copy of Office 2000, Access lists the ROMAN worksheet function in its help pages but does not seem to actually support it (I had the same problem with NETWORKDAYS). However, it still seems to work in Excel.

Same for me, I thought it was because I had installation problems.

Nice to see someone having the same problem.:)

[eed]

Norie,

At least in my 2003 version of Access here at home, that worksheet function works like a charm. I hope it carries over as well into my 2000 Access at work. I'll consider this solved for now.

Thanks!

~ eed

[Norie]

eed

I have Access 2000 and it works for me.

The only difference I imagine might be the version of the object library.

[xCav8r]

xCav8r, that is an option that has occurred to me, but my past experiences with automated mail merge from Access to Word have been sketchy. One second they work beautifully, and the next, they blow up. I am creating a very long and complex report. But, so far, I have been able to successfully control through Access all the necessary details of table gridlines, page breaks, page number formatting, etc. The Roman numeral page numbers in the introductory section have been my only sticking point so far, so I'm trying not to resort to a mail merge solution yet if I can help it.

I tend to favor Word first, especially when it comes to creating long and complex reports. You can waste a lot of time trying to get Access to do things that Word will do quite quickly and often better. It also makes for a better electronic version, since you lose certain elements when you convert your report to RTF. My :2p:

[eed]

I tend to favor Word first, especially when it comes to creating long and complex reports. You can waste a lot of time trying to get Access to do things that Word will do quite quickly and often better. It also makes for a better electronic version, since you lose certain elements when you convert your report to RTF. My :2p:

Very valid points, although in this case, we never intend to convert reports to RTF. My bosses want to prevent, as much as possible, any editing of the data outside the database. Reports will be exported only to Snapshot and PDF formats. So, if we mail merged into Word, we'd still have to convert to PDF, then delete the Word file. It would make certain formatting steps easier, but would seem to complicate the overall process in this very specific case. In other cases, I would almost certainly go your way and use the mail merge to Word for formatting purposes.

I don't know. There are certainly advantages to using the mail merge. But my bosses don't want RTF output, and they pay me to give them what they ask for. *grin*

~ eed

[eed]

eed

I have Access 2000 and it works for me.

The only difference I imagine might be the version of the object library.

Yup! Works swell in Access 2000 with a ref to the Excel 9.0 object library. Thanks again!

~ eed

[chocobochick]

Odd. Even with a reference to the Excel 9.0 object library, I can't get it to work. Am I missing something? All you have to do is go to Tools/References and check the box, right?

[Norie]

How are you trying it?

[chocobochick]

I've tried the syntax ROMAN(number, format) in both VBA and a query criteria expression.

Do I need to explicitly point to the Excel object library for each function call? Something like ExcelObject.ROMAN()?

[Norie]

Did you not see the code I posted?

[chocobochick]

:doh: Err... nope. That specific detail had escaped me. Sorry about that, but it works now!

So any idea how I'd go about referencing the NETWORKDAYS function from the Analysis Toolpak add-in?

[Norie]

Not really.

I can't even remember how, if you can, access NETWORKDAYS from Excel VBA.

I'll have a look into it.