If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Suppress Leading Zero
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 |
#2
|
|||
|
|||
Suppress Leading Zero
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. -- Duane Hookom Microsoft Access MVP "Mommybear" wrote: 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 |
#3
|
|||
|
|||
Suppress Leading Zero
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 Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "Mommybear" wrote: 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 |
#4
|
|||
|
|||
Suppress Leading Zero
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) -- Duane Hookom MS Access MVP "Mommybear" wrote in message ... 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 Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "Mommybear" wrote: 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 |
#5
|
|||
|
|||
Suppress Leading Zero
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. "Duane Hookom" wrote: 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) -- Duane Hookom MS Access MVP "Mommybear" wrote in message ... 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 Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "Mommybear" wrote: 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 |
#6
|
|||
|
|||
Suppress Leading Zero
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. "Mommybear" wrote: 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. "Duane Hookom" wrote: 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) -- Duane Hookom MS Access MVP "Mommybear" wrote in message ... 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 Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "Mommybear" wrote: 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 |
#7
|
|||
|
|||
Suppress Leading Zero
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. -- Duane Hookom Microsoft Access MVP "Mommybear" wrote: 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. "Mommybear" wrote: 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. "Duane Hookom" wrote: 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) -- Duane Hookom MS Access MVP "Mommybear" wrote in message ... 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 Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "Mommybear" wrote: 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 |
#8
|
|||
|
|||
Suppress Leading Zero
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. "Duane Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "Mommybear" wrote: 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. "Mommybear" wrote: 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. "Duane Hookom" wrote: 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) -- Duane Hookom MS Access MVP "Mommybear" wrote in message ... 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 Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "Mommybear" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|