Supressing leading zeros

[kojak43]

I am sorting by page number (a field) Since there are more than 100 pages, I have to use 001-100 so when it sorts, 100 does not appear before 12.
I have selected asending sort in the report for page number. However, it shows 012. How can I get it to show 12?

Thanks
k

 

[SydneyGeek]

Sound like you may have the page number field defined as text -- is that correct?
Either convert it to a number, then sorting will be numeric, or use a text expression to strip leading zeros. The exression for stripping the zeros could be a bit messy. I'd go the Number route.

Denis

 

[kojak43]

Yes, you are correct. I have it set as text. For a couple of reasons. one, if I am not going to proform math, I usually make fields like this text. I'll have to remember to be less rigid when assigning formats.

The real problem is these pages are from a brochure that lists all stock photo licensing used in that particular brochure. Brochures usually have covers that are numbered separately from the inside pages. That is, you can not number a brochure starting with the cover as page one, as the inside of the brochure also has a page one. Therefore there is Cover1, Cover2, Cover3 and Cover4. Then there is Page001 thru Page112. And if the brochure has a couple of bound-in insert cards there is InsertA1, InsertA2, InsertB1 and InsertB2. So when I do a sort, Covers appear first, then inserts then pages.

Now I am stuck with those pesky leading 0's.

 

[CT Witter]

To extract just the number part without the leading zeros you can use the CDbl() function in a query or report. For example:

Convert: CDbl([Field1]) Where field one has 001,002,etc as text.

Result 1,2, etc.

HTH,
CT

 

[kojak43]

Thanks folks. I could not get the DBl Convert to work.

I added a new field called page position. Made the lookup for page, cover and insert. Changed the text to numbers in the page number field and it works fine. No more leading 0's.