Ordering by number and text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DanWH - 17 Jan 2008 16:35 GMT

I use a report to print out checklists that in the detail section have item
number to delineate each checklist item. The item numbers as an example are
1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is
grouped by checklist section and the grouping works perfectly but when the
report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11,
1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9.

Obviously I want the the order to be in proper numerical sequence where 1-10
comes after 1-9. but even replacing the '-' with a decimal point doesn't
work. I can't change the field type to a number because of the use of
letters.

Is there a relatively easy way to get the order I need from the report.

Thanks
Dan

Reply to this Message

Dirk Goldgar - 17 Jan 2008 16:54 GMT

>I use a report to print out checklists that in the detail section have item
> number to delineate each checklist item. The item numbers as an example
[quoted text clipped - 13 lines]
>
> Is there a relatively easy way to get the order I need from the report.

Just a thought, but you might order by two values:

Val(Replace([ItemNumber], "-", "."))
[ItemNumber]

So the first would be the numeric value of the translated item number, and
the second would be the item number alone. That should cover both numeric
sequencing and text suffixes -- if it works, of course. <g>

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

DanWH - 17 Jan 2008 19:44 GMT

Dirk, that worked perfectly for the form, but it won't do anything when I try
to implement in the report design. I'm trying to figure it out, but any help
you can offer will be appreciated.

Dan

> >I use a report to print out checklists that in the detail section have item
> > number to delineate each checklist item. The item numbers as an example
[quoted text clipped - 22 lines]
> the second would be the item number alone. That should cover both numeric
> sequencing and text suffixes -- if it works, of course. <g>

Reply to this Message

Dirk Goldgar - 18 Jan 2008 02:39 GMT

> Dirk, that worked perfectly for the form, but it won't do anything when I
> try
> to implement in the report design. I'm trying to figure it out, but any
> help
> you can offer will be appreciated.

You'd do this in the report's Sorting & Grouping dialog. Underneath the
actual last group entry, create one entry for the Val() expression, and a
second one for the field. Neither of these entries should have a group
header or footer.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

DanWH - 18 Jan 2008 13:21 GMT

Dirk,

When I enter Val(Replace([Item Number],"-",".")) in a sort group as you
asked, I get the following error:

" Syntax error (comma) in query expression [Val(Replace([Item
Number],"-","].["))]'. "

Please advise and I do appreciate all your help.
Dan

> > Dirk, that worked perfectly for the form, but it won't do anything when I
> > try
[quoted text clipped - 6 lines]
> second one for the field. Neither of these entries should have a group
> header or footer.

Reply to this Message

Dirk Goldgar - 18 Jan 2008 15:03 GMT

> Dirk,
>
[quoted text clipped - 3 lines]
> " Syntax error (comma) in query expression [Val(Replace([Item
> Number],"-","].["))]'. "

Hmm. Try sticking an equals sign on the front:

=Val(Replace([Item Number],"-","."))

I'm trusting that the name of the field is, in fact, "Item Number".

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

DanWH - 18 Jan 2008 15:56 GMT

Dirk,

It worked perfectly but only after I made a slight change

Instead of: =Val(Replace[ItemNumber],"-","."))

I used: =Val(Replace[ItemNUmber],"-",""))

Then put the next group level as just ItemNumber.

Thank You Very Much.

Dan

> > Dirk,
> >
[quoted text clipped - 9 lines]
>
> I'm trusting that the name of the field is, in fact, "Item Number".

Reply to this Message

Dirk Goldgar - 18 Jan 2008 18:05 GMT

> Dirk,
>
[quoted text clipped - 5 lines]
>
> Then put the next group level as just ItemNumber.

Great! But now that I think about it further, I'm still not sure it's
right -- I may have given you a bad expression to begin with. Could you
please verify that item numbers 1-9 and 1-10 sort correctly? I have a
feeling they won't.

> Thank You Very Much.

You're welcome ... but we may not be done.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

BruceM - 18 Jan 2008 20:50 GMT

Dirk, I think you're right, since 1.1 (1-10) will be in front of 1.9 (1-9).
I didn't have any trouble with report sorting and grouping, by the way.
I parsed the field. In query design view, two calculated fields:
FirstPart: Val(Left([ItemNumber],InStr([ItemNumber],"-")-1))
SecondPart:
Val(Right([ItemNumber],Len([ItemNumber])-InStr([ItemNumber],"-")))
Then I ordered by those two fields, followed by ItemNumber. I think it
works as intended, but I tested quickly, so I may not have considered all
possibilities.
I would probably make a query with those fields, then use those fields in
the report sorting and grouping, rather than add the expresssions directly
to the sorting and grouping, but I expect it doesn't matter except as a
personal preference choice.

>> Dirk,
>>
[quoted text clipped - 14 lines]
>
> You're welcome ... but we may not be done.

Reply to this Message

DanWH - 18 Jan 2008 21:42 GMT

Dirk, Everything sorts correctly it sorts 1-8 , 1-9, 1-10, 1-11 and so forth.
I still have a lot of data yet to input but with all the data that is in
there now, it is working the way I want it to. Hopefull it will keep working
correctly.

Thanks
Dan

> > Dirk,
> >
[quoted text clipped - 14 lines]
>
> You're welcome ... but we may not be done.

Reply to this Message