HOW CAN I SORT SEQUENTIALY IN A REPORT?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

the question that I have is . I have to sort a report in a sequential matter for exaple of what I get now

JobNumber
04-0609
04-0610
04-06100
04-06101 etc.....

when I need to get

JobNumber
04-0609
04-0610
04-0611
04-0612 etc.....

I was thinking if I fix my query the report would match the query information but it dont so how can I sort sequentialy in a report.

Thanks in advance
Rafael
 
Assuming that the job numbers are in a table, format the
Job Number field as text. After that, sort as needed in
report grouping and sorting. Base the report on a query
as needed (for instance, to combine first and last names);
otherwise you can base it on a table. If I understand you
correctly, report sorting and grouping (rather than a
query) is how you will manage the order.
-----Original Message-----
the question that I have is . I have to sort a report in
Click to expand...
a sequential matter for exaple of what I get now
JobNumber
04-0609
04-0610
04-06100
04-06101 etc.....

when I need to get

JobNumber
04-0609
04-0610
04-0611
04-0612 etc.....

I was thinking if I fix my query the report would match
Click to expand...
the query information but it dont so how can I sort
sequentialy in a report.
 
rafaeljsg said:
the question that I have is . I have to sort a report in a sequential matter for exaple of what I get now

JobNumber
04-0609
04-0610
04-06100
04-06101 etc.....

when I need to get

JobNumber
04-0609
04-0610
04-0611
04-0612 etc.....

I was thinking if I fix my query the report would match the query information but it dont so how can I sort sequentialy in a report.
Click to expand...

You have a text jobnumber, but you want to sort it as if it
were a number. The job number shold have been designed as
two fields - a prefix integer (the 4) and a suffix long (the
609) so the sorting could be done on the two separate
numeric values.

Lacking that, you need to parse the job number into to parts
and convert each part to a number. The report's Sorting and
Grouping will thwn have two entries:

=Val(Left(JobNumber, 2)
and
=Val(Mid(JobNumber, InStr(JobNumber, "-") + 1))
 
I think what you want is sort on the number after "04-"
and the sort you want is in the numerical value sequence.
It appear currently your Job Number is stored a as text
field and it is sorted as text.
What you can do is create the following in your query and
the sorting on this will than be as you indicate.
Val(Right([JobNumber],((Len([JobNumber])-(InStr
([JobNumber],"-"))))))
all on one line.
This will sort as you indicated, however, keep in mind if
you have 04-0011 and a record 04-011 these will be sorted
adjacent since the numeric value of these is the same.
Hope this helps.
Fons
-----Original Message-----
the question that I have is . I have to sort a report in
Click to expand...
a sequential matter for exaple of what I get now
JobNumber
04-0609
04-0610
04-06100
04-06101 etc.....

when I need to get

JobNumber
04-0609
04-0610
04-0611
04-0612 etc.....

I was thinking if I fix my query the report would match
Click to expand...
the query information but it dont so how can I sort
sequentialy in a report.
 
The query displays data in the order you need? Have you
tried sorting the report based on the same criteria? In
report design view, click View > Sorting and Grouping.
Select the sort field and the criteria there.
-----Original Message-----
hi, the j# are set as a text field but and I get the data
Click to expand...
from a query that was sorted how I want it to show in the
report but the report des what I show before while the
query does what I want the report to do. I try every thing
I know and I cant get it to do what I want.
 
The query displays data in the order you need?
yes

have you tried sorting the report based on the same criteria?
yes

In report design view, click View > Sorting and Grouping.
Select the sort field and the criteria there.

I have done this and does nothing for me actually the sample is after I did the sorting.

by the way the j# mean 04 is year 06 is moth nad the las two to three digits are the designated job #
 
I just did a test, and it works as intended. The only
reason for it not to work for you is that you are doing
something other than what I understand. Is each number
(04-0609, 04-0610, etc.) part of a separate record? What
is the structure of the table containing those numbers?
Try this: Create a new table in design view with three
fields: ID (autonumber), JobNum, Date (or whatever).
Leave data type for ID at the default, set JobNum as text,
and Date as Date/Time. Set ID as the primary key. Switch
to Datasheet view, and create a few records, using the
numbers in your example. Save the table, and create a
report in design view. Click View > Properties. Click
the Data tab, then click the down arrow next to Record
Source. Select your new table. Drag JobNum and Date next
to each other on the Detail section of the design grid,
and set the height of the Detail section to just tall
enough for your fields. Click View > Sorting and
Grouping. Click in the first row of Field/Expression.
Click the down arrow, and select JobNum. Look at the
report in Print Preview.
If several job numbers are part of the same record,
sorting is not really an option. Records can be sorted,
but fields can't be rearranged. If this is the case,
provide details of your database structure.
-----Original Message-----
The query displays data in the order you need?
yes

have you tried sorting the report based on the same criteria?
yes

In report design view, click View > Sorting and Grouping.
Select the sort field and the criteria there.

I have done this and does nothing for me actually the
Click to expand...
sample is after I did the sorting.
by the way the j# mean 04 is year 06 is moth nad the las
Click to expand...
two to three digits are the designated job #
 
Back
Top