Sorting Problem

[jbento]

All I have 2 fields on a report that I want to sort by. 1 is a date field and the other is a text field. The name of the date field is: iecdNumber and the name of the text field is: iecd_number.

The iecd_number field has to be a text field because users are allowed to enter numbers and letters.

I want to sort ascending on both, but it doesn't sort correctly, because of the letters in the iecd_number field.

Example:

c03-0107
c03-0017b
c03-0065a

I want that to sort like this:
c03-0017b
c03-0065a
c03-0107

c03 = iecdNumber (date field)
0107, etc. = iecd_number (text field)

I need to keep the iecd_number as a text field.

How can I get this to sort correctly? Is there a way?

Please help.

Thanks in advance.

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[BNPMike]

In your report query you need to create a another field which pulls out the first 4 characters of the number, and one which has the final letter. Sort on date, substring, final letter. You don't need to display these fields on the final report, but you can use them to order the report correctly.

 

[dhookom]

Jerome,
This doesn't make sense since "c03" is not a date field:
c03 = iecdNumber (date field)
Do you realize that you must do your sorting in the report's sorting and grouping dialog?
The example of how you want to sort is exactly how the records would sort.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[BNPMike]

The example of how you want to sort is exactly how the records would sort.

No they won't, Duane. The a or b at the end makes the string longer so it's really like this

c03-0017b
c03-0065a
c03-0107

which sorts:

c03-0107
c03-0017b
c03-0065a

 

[dhookom]

I don't get it. BNPMIke, where did you get a space in front of one of your records? Also, there has been mention made of a date field but I can't see any date values. The following values should sort just as displayed below.

c03-0017b
c03-0065a
c03-0107

"0017" would always sort ascending before "0107". String values never sort based on length of the string. Each character is compared beginning from the first character to the next to the next ...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jbento]

All,
The field that has the value "c03" has been formatted like so: \cyy and it is a date field.

I would like to do the suggestion from BNPMike about stripping characters, but I would like to do this on a form with this new field hidden.

Maybe I could create another field on a form which could be called: fldStripped. This would be hidden.

I would like to have the fldStripped.Value = iecd_number, but I want whatever the ending letter is to be deleted.

For instance, iecd_number is: 0072a, so the code should take the a off and just have 0072 in the fldStripped.

How can this be accomplished?

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[dhookom]

There is no reason to strip anything as long as the numeric part is always 4 characters. Doing any stripping would probably be a waste of time if this was the case. I expect your issue is thinking that the way a date field appears is also the way it will be stored or sort. I would suggest that you set up two levels in the sorting and grouping:
=Year([iecdNumber])
[iecd_number]


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[BNPMike]

Sorry, Duane. You're right.

 

[jbento]

The problem I was having was with the iecdNumber field which was formatted with \cyy. Because of the formatting I couldn't see the actual date until I clicked in that field. The problem was some of the dates wasn't in the correct sort order, so I just simply changed some of the dates to force them to sort correctly.

Thanks to all for your inputs and help. This was so easy I just couldn't see it

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[dhookom]

jbento,
That is what I expected was happening and why I suggested you sort first by:
=Year([iecdNumber])
Editing data to obtain the proper sorting isn't generally a good practice unless you really needed the data edited.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.