A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Suppress Leading Zero



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2010, 07:09 PM posted to microsoft.public.access.reports
Mommybear
external usenet poster
 
Posts: 37
Default 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  
Old April 28th, 2010, 08:12 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old April 28th, 2010, 09:19 PM posted to microsoft.public.access.reports
Mommybear
external usenet poster
 
Posts: 37
Default 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  
Old April 29th, 2010, 02:28 AM posted to microsoft.public.access.reports
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default 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  
Old April 29th, 2010, 06:42 PM posted to microsoft.public.access.reports
Mommybear
external usenet poster
 
Posts: 37
Default 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  
Old April 29th, 2010, 06:59 PM posted to microsoft.public.access.reports
Mommybear
external usenet poster
 
Posts: 37
Default 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  
Old April 29th, 2010, 10:11 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old April 30th, 2010, 02:07 PM posted to microsoft.public.access.reports
Mommybear
external usenet poster
 
Posts: 37
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.