Prev: Strange Problem With DoCmd.OutputTo When Generating a PDF File.
Next: Report Shows ID instead of Text
From: Mommybear on 29 Apr 2010 13:59
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" <Mommybear(a)discussions.microsoft.com> wrote in message
> > news:14DC802B-7BD5-46B7-834F-9A317B323143(a)microsoft.com...
> > > 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
> >
From: Duane Hookom on 29 Apr 2010 17:11
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" <Mommybear(a)discussions.microsoft.com> wrote in message
> > > news:14DC802B-7BD5-46B7-834F-9A317B323143(a)microsoft.com...
> > > > 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
> > >
From: Mommybear on 30 Apr 2010 09:07
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" <Mommybear(a)discussions.microsoft.com> wrote in message
> > > > news:14DC802B-7BD5-46B7-834F-9A317B323143(a)microsoft.com...
> > > > > 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
> > > >
First  |  Prev  | 
Pages: 1 2
Prev: Strange Problem With DoCmd.OutputTo When Generating a PDF File.
Next: Report Shows ID instead of Text