Suppress Leading Zero

[Mommybear]

I have a report that is sorted by Floor, Department, then by Room. My floors
are 1-12. In order to get them to sort properly I have them in the database
as: 01, 02, 03, etc else they sort like this 1, 10, 11, 12, 2, 3, etc.

These are showing on my report as:
01, 02, 03, etc and I only want them to appear as a single digit, 1, 2, 3,
etc. I tried setting the format to ## but it still shows the leading zero.

Any suggestions.

Dawn

 

[Duane Hookom]

You can wrap the Floor field in the Val() function. This would also aid in
sorting since the Val() of 2 would sort before the Val() of 13.

 

[Mommybear]

Doing this gave me a result of #### in my report. I did forget to mention
that my format was text and not numeric. When I tried to change the format,
I got a "not enough space" error. I have over 33k records. I do have some
alphanumeric values as well (B1, B2). Which should be sorting first. "Duane

 

[Duane Hookom]

Did you change the name of the text box? You can't have a text box named
"Floor" with a control source like:
=Valu(Floor)

 

[Mommybear]

Floors 1-9 worked great however, 10, 11, 12, were excluded and B1 and B2
appeared as an Error as did the floors with no value.

 

[Mommybear]

Originally when I posted this question, I changed my values to be 01, 02, 3,
4, ...10, 11c so they would appear before 10, 11... With your solution, my
floors were appearing 1, 2, 10, 11, 3, 4. I removed the leading zeros and
tried again and they are back to printing 1, 10, 11, 12, 2, 3.... What is
the best way to make this work properly, change all my data to have a leading
zero, remove the zeros? My deadline to have this working is tomorrow.

 

[Duane Hookom]

Your original post stated "My floors are 1-12". Using the Val() function
against values like "1", "2", etc will work if you do it correctly and don't
have letters in the mix.

If you really want to have "B" values, then add another field to your table
to identify to sort order. Another solution would be to create a small
function or expression that correctly handles the letters and text.

You decide what text values you want to store and then if you have a
problem, come back here with a description of your issue.

 

[Mommybear]

I didn't realize I had alpha values until after I tried you suggestion and
got the errors. (I have 30,000+ records) It's not a matter of really wanting
to have "B" values. I have a basement 'B1' and a sub-basement 'B2' which is
a valid floor. My data was given to me this way and I can't change that.
Per your recent suggestion, I did create another field on my query which
converted the "Floor values" to a numeric field and sorted that way. It
works perfectly. Thank you.