Ordering by number and text

[DanWH]

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

 

[Dirk Goldgar]

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.

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>

 

[DanWH]

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
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.

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>

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

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.

 

[DanWH]

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 Goldgar]

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],"-","].["))]'. "

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".

 

[DanWH]

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,

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],"-","].["))]'. "

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".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

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.

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.

 

[BruceM]

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,

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.

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[DanWH]

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,

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.

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)