Sorting Issues!

  • Thread starter Thread starter Ascheman
  • Start date Start date
A

Ascheman

Thanks to fredg in my below post. Worked perfectly, but
now I am stuck again with no idea how to fix them problem.
When my reports generate all the records starting with 0
come before any of the other records. Where im stuck is
trying to figure out how to get it to sort them so it
thinks that a 0 is greater then a 9. Here is a sample of
what I am looking at:

0006
0006A
662
738
748
9814
9920

What I need to happen is for the records beggining with 0
to come after the records starting with 9 like this:

662
738
748
9814
9920

Please if anyone knows a way to fix this I would be
greatly indebted to you. If the fix is possible through vb
its much more preferable.
Thanks in Advance.
C.Ascheman
 
you say, "how to get it to sort them so it thinks that a 0 is greater then a
9" but in your example, you simply left off the numbers starting with "0".
The sort in your two examples is the same.

What are you asking for?

also, a 0 is NOT greater than a 9?!?!?



Thanks to fredg in my below post. Worked perfectly, but
now I am stuck again with no idea how to fix them problem.
When my reports generate all the records starting with 0
come before any of the other records. Where im stuck is
trying to figure out how to get it to sort them so it
thinks that a 0 is greater then a 9. Here is a sample of
what I am looking at:

0006
0006A
662
738
748
9814
9920

What I need to happen is for the records beggining with 0
to come after the records starting with 9 like this:

662
738
748
9814
9920

Please if anyone knows a way to fix this I would be
greatly indebted to you. If the fix is possible through vb
its much more preferable.
Thanks in Advance.
C.Ascheman
 
Sorry Rick B and everyone else for the parts I missed. The
report is sorted by Unit_Number in ascending order. In my
previous post I had help in removing the leading 2 off of
some of the records (which my boss had put there prior to
me coming to work here).
The number are listed like this:
0303A
0304
0306
662
738
9814
9920

What I need it to look like is this:
662
738
9814
9920
0303A
0304

I need someway to fool the report into thinking that 0 is
greater than 9 so the report is in the order my boss wants
it in. I have tried everything I can think of just
apparantly dont have enough years under my vb belt yet. So
if any of you out there can help me to get the report to
sort so it thinks that 0 is greater then 9 (even though
its not) then I thank you.
C.Ascheman
 
I apologize for leaving out parts. The end result is
supposed to look like this:
662
738
748
9814
9920
0006
0006A

The 0 records (0006 and 0006A) in the database have a 2
placed before them. This was done prior to me coming to
work for the company I now work for. With help I have
managed to remove the leading 2's (Thanks again fredg),
but now my boss wants the report sorted as I show above.
In other words I need a way (vb code or otherwise) to make
the system think that 0 is greater than a 9 so it will
sort as he wishes. Please if anyone can help with this I
will be greatly appreciative and indebted.
C.Ascheman
 
The Order By clause is part of the query. If your report does not currently use a querydef as its RecordSource, you'll need to create one.

Rather than modifying the Order By clause of the query, I'll give you instructions on how to create the query and use the report to sort the data.

1. Use the query window to create a query that selects only the columns you need for the report.
2. Include any selection criteria to limit the rows returned.
3. Add a calculated field to the query - In the field cell of the first empty column of the querydef place something like:
SortField:IIf(Left(YourField, 1) = "0", 9, 0)
5. Save the query.
6. Open the report in design view.
7. On the data tab, change the RecordSource to the name of your saved querydef. You can choose it from the drop down list.
8. Open the report's sorting and grouping properties.
9. Add SortField as the first field in the sorting list.
10. Add subsequent fields in order of priority.
11. Save the report.
12. Run it
 
I apologize for leaving out parts. The end result is
supposed to look like this:
662
738
748
9814
9920
0006
0006A

The 0 records (0006 and 0006A) in the database have a 2
placed before them. This was done prior to me coming to
work for the company I now work for. With help I have
managed to remove the leading 2's (Thanks again fredg),
but now my boss wants the report sorted as I show above.
In other words I need a way (vb code or otherwise) to make
the system think that 0 is greater than a 9 so it will
sort as he wishes. Please if anyone can help with this I
will be greatly appreciative and indebted.
C.Ascheman
Click to expand...

The method will depend upon how you wish to have some intermediate
values sorted.
a) or b)
662 662
738 738
739 729
740 740
0304 9814
0306 9920
0306A 0006
9814 0006A
9920 0039
0039 0304
0006 0306
0006A 0306A

Notice the difference in sorting after #740.
Let me know which sort order you wish.
 
fredg, the sort order im needing is the one you have
listed in B. That is exactly how im needing them sorted. I
have tried several ways to get them to sort like that but
no go ... I cant seem to get it right. Please if you can
help me with this I would greatly appreciate it.
Thanks again,
C.Ascheman
 
fredg, the sort order im needing is the one you have
listed in B. That is exactly how im needing them sorted. I
have tried several ways to get them to sort like that but
no go ... I cant seem to get it right. Please if you can
help me with this I would greatly appreciate it.
Thanks again,
C.Ascheman
Click to expand...

Base your report upon a query.
After you have all your fields included, add a New Column to the
query.

SortThis:IrregularSort([FieldName])

Make sure the Show check box IS checked.

Then copy this function into a new Public Module:

Public Function IrregSort(FieldIn As String) As String
Dim intX As Integer
Dim strNew As String
intX = Asc(FieldIn)
If intX = 48 Then
FieldIn = "z" & FieldIn
End If
IrregSort = FieldIn

End Function
=====
Save and name the module whatever you want, but NOT IrregSort.

Open your Report in Design View.
Click on View + Sorting and Grouping

On the top level Field/Expression box, write
SortThis
Select Ascending as the sorting order.

That should be all you need.
There is no need to sort the query as it's sort order is irrelevant in
the report.

Let me know how you make out.
 
-----Original Message-----
fredg, the sort order im needing is the one you have
listed in B. That is exactly how im needing them sorted. I
have tried several ways to get them to sort like that but
no go ... I cant seem to get it right. Please if you can
help me with this I would greatly appreciate it.
Thanks again,
C.Ascheman
this
I that
a with
0 through
vb
Click to expand...

Base your report upon a query.
After you have all your fields included, add a New Column to the
query.

SortThis:IrregularSort([FieldName])

Make sure the Show check box IS checked.

Then copy this function into a new Public Module:

Public Function IrregSort(FieldIn As String) As String
Dim intX As Integer
Dim strNew As String
intX = Asc(FieldIn)
If intX = 48 Then
FieldIn = "z" & FieldIn
End If
IrregSort = FieldIn

End Function
=====
Save and name the module whatever you want, but NOT IrregSort.

Open your Report in Design View.
Click on View + Sorting and Grouping

On the top level Field/Expression box, write
SortThis
Select Ascending as the sorting order.

That should be all you need.
There is no need to sort the query as it's sort order is irrelevant in
the report.

Let me know how you make out.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...


Fred,
I tried what you said and no go. It still sorts them in
the same order. If you have any other ideas of how to get
the records starting with 0 to come after those starting
with 9 I would greatly appreciate it. Just to re-iterate.
Those numbers beggining with 0 actually begin with a 2 but
are removed via code as those 2's were placed on their by
my boss years before I started working here (using
Microsoft Works 4.5), and he has recently asked me to
upgrade it to Microsoft Access, and to remove the 2's but
to also have those records come after those beginning with
9. So originally they are 20006 and after some code
intervention it shows in the report as 0006 but is at the
beginning of the report when it needs to be at the end of
report (as its newer equipment). Thanks again for all your
help Fred and if you can help me to figure this one out I
will be greatly indebted to you.
C.Ascheman
 
If your only criteria is to have all the 'numbers' starting with a '0' to
follow all 'numbers' starting with a '9' and otherwise sorted numerically,
you only need to do the following:

In the top level of the sorting and grouping box enter this:

=[Number] Like ("0*")

and set the order to descending. Of course, use your own fieldname for the
'number'.

LaVern



Ascheman said:
-----Original Message-----
fredg, the sort order im needing is the one you have
listed in B. That is exactly how im needing them sorted. I
have tried several ways to get them to sort like that but
no go ... I cant seem to get it right. Please if you can
help me with this I would greatly appreciate it.
Thanks again,
C.Ascheman

-----Original Message-----
On Tue, 3 Aug 2004 08:56:57 -0700, Ascheman wrote:

I apologize for leaving out parts. The end result is
supposed to look like this:
662
738
748
9814
9920
0006
0006A

The 0 records (0006 and 0006A) in the database have a 2
placed before them. This was done prior to me coming to
work for the company I now work for. With help I have
managed to remove the leading 2's (Thanks again fredg),
but now my boss wants the report sorted as I show
above.
In other words I need a way (vb code or otherwise) to
make
the system think that 0 is greater than a 9 so it will
sort as he wishes. Please if anyone can help with this
I
will be greatly appreciative and indebted.
C.Ascheman

-----Original Message-----
you say, "how to get it to sort them so it thinks that
a
0 is greater then a
9" but in your example, you simply left off the numbers
starting with "0".
The sort in your two examples is the same.

What are you asking for?

also, a 0 is NOT greater than a 9?!?!?



Thanks to fredg in my below post. Worked perfectly, but
now I am stuck again with no idea how to fix them
problem.
When my reports generate all the records starting with 0
come before any of the other records. Where im stuck is
trying to figure out how to get it to sort them so it
thinks that a 0 is greater then a 9. Here is a sample of
what I am looking at:

0006
0006A
662
738
748
9814
9920

What I need to happen is for the records beggining with
0
to come after the records starting with 9 like this:

662
738
748
9814
9920

Please if anyone knows a way to fix this I would be
greatly indebted to you. If the fix is possible through
vb
its much more preferable.
Thanks in Advance.
C.Ascheman


.


The method will depend upon how you wish to have some
intermediate
values sorted.
a) or b)
662 662
738 738
739 729
740 740
0304 9814
0306 9920
0306A 0006
9814 0006A
9920 0039
0039 0304
0006 0306
0006A 0306A

Notice the difference in sorting after #740.
Let me know which sort order you wish.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...

Base your report upon a query.
After you have all your fields included, add a New Column to the
query.

SortThis:IrregularSort([FieldName])

Make sure the Show check box IS checked.

Then copy this function into a new Public Module:

Public Function IrregSort(FieldIn As String) As String
Dim intX As Integer
Dim strNew As String
intX = Asc(FieldIn)
If intX = 48 Then
FieldIn = "z" & FieldIn
End If
IrregSort = FieldIn

End Function
=====
Save and name the module whatever you want, but NOT IrregSort.

Open your Report in Design View.
Click on View + Sorting and Grouping

On the top level Field/Expression box, write
SortThis
Select Ascending as the sorting order.

That should be all you need.
There is no need to sort the query as it's sort order is irrelevant in
the report.

Let me know how you make out.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...


Fred,
I tried what you said and no go. It still sorts them in
the same order. If you have any other ideas of how to get
the records starting with 0 to come after those starting
with 9 I would greatly appreciate it. Just to re-iterate.
Those numbers beggining with 0 actually begin with a 2 but
are removed via code as those 2's were placed on their by
my boss years before I started working here (using
Microsoft Works 4.5), and he has recently asked me to
upgrade it to Microsoft Access, and to remove the 2's but
to also have those records come after those beginning with
9. So originally they are 20006 and after some code
intervention it shows in the report as 0006 but is at the
beginning of the report when it needs to be at the end of
report (as its newer equipment). Thanks again for all your
help Fred and if you can help me to figure this one out I
will be greatly indebted to you.
C.Ascheman
Click to expand...
 
For clarification, my solution will work only if all of the 'number's start
with a numeral.

LaVern

LGC said:
If your only criteria is to have all the 'numbers' starting with a '0' to
follow all 'numbers' starting with a '9' and otherwise sorted numerically,
you only need to do the following:

In the top level of the sorting and grouping box enter this:

=[Number] Like ("0*")

and set the order to descending. Of course, use your own fieldname for the
'number'.

LaVern



Ascheman said:
-----Original Message-----
On Tue, 3 Aug 2004 15:19:50 -0700, Ascheman wrote:

fredg, the sort order im needing is the one you have
listed in B. That is exactly how im needing them sorted. I
have tried several ways to get them to sort like that but
no go ... I cant seem to get it right. Please if you can
help me with this I would greatly appreciate it.
Thanks again,
C.Ascheman

-----Original Message-----
On Tue, 3 Aug 2004 08:56:57 -0700, Ascheman wrote:

I apologize for leaving out parts. The end result is
supposed to look like this:
662
738
748
9814
9920
0006
0006A

The 0 records (0006 and 0006A) in the database have a 2
placed before them. This was done prior to me coming to
work for the company I now work for. With help I have
managed to remove the leading 2's (Thanks again fredg),
but now my boss wants the report sorted as I show
above.
In other words I need a way (vb code or otherwise) to
make
the system think that 0 is greater than a 9 so it will
sort as he wishes. Please if anyone can help with this
I
will be greatly appreciative and indebted.
C.Ascheman

-----Original Message-----
you say, "how to get it to sort them so it thinks that
a
0 is greater then a
9" but in your example, you simply left off the numbers
starting with "0".
The sort in your two examples is the same.

What are you asking for?

also, a 0 is NOT greater than a 9?!?!?



Thanks to fredg in my below post. Worked perfectly, but
now I am stuck again with no idea how to fix them
problem.
When my reports generate all the records starting with 0
come before any of the other records. Where im stuck is
trying to figure out how to get it to sort them so it
thinks that a 0 is greater then a 9. Here is a sample of
what I am looking at:

0006
0006A
662
738
748
9814
9920

What I need to happen is for the records beggining with
0
to come after the records starting with 9 like this:

662
738
748
9814
9920

Please if anyone knows a way to fix this I would be
greatly indebted to you. If the fix is possible through
vb
its much more preferable.
Thanks in Advance.
C.Ascheman


.


The method will depend upon how you wish to have some
intermediate
values sorted.
a) or b)
662 662
738 738
739 729
740 740
0304 9814
0306 9920
0306A 0006
9814 0006A
9920 0039
0039 0304
0006 0306
0006A 0306A

Notice the difference in sorting after #740.
Let me know which sort order you wish.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.


Base your report upon a query.
After you have all your fields included, add a New Column to the
query.

SortThis:IrregularSort([FieldName])

Make sure the Show check box IS checked.

Then copy this function into a new Public Module:

Public Function IrregSort(FieldIn As String) As String
Dim intX As Integer
Dim strNew As String
intX = Asc(FieldIn)
If intX = 48 Then
FieldIn = "z" & FieldIn
End If
IrregSort = FieldIn

End Function
=====
Save and name the module whatever you want, but NOT IrregSort.

Open your Report in Design View.
Click on View + Sorting and Grouping

On the top level Field/Expression box, write
SortThis
Select Ascending as the sorting order.

That should be all you need.
There is no need to sort the query as it's sort order is irrelevant in
the report.

Let me know how you make out.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...


Fred,
I tried what you said and no go. It still sorts them in
the same order. If you have any other ideas of how to get
the records starting with 0 to come after those starting
with 9 I would greatly appreciate it. Just to re-iterate.
Those numbers beggining with 0 actually begin with a 2 but
are removed via code as those 2's were placed on their by
my boss years before I started working here (using
Microsoft Works 4.5), and he has recently asked me to
upgrade it to Microsoft Access, and to remove the 2's but
to also have those records come after those beginning with
9. So originally they are 20006 and after some code
intervention it shows in the report as 0006 but is at the
beginning of the report when it needs to be at the end of
report (as its newer equipment). Thanks again for all your
help Fred and if you can help me to figure this one out I
will be greatly indebted to you.
C.Ascheman
Click to expand...
Click to expand...
 
Back
Top