Sort problem in report

[toolman2]

I have created a report that has field ("pocket&quot by which the rows in the detail section are sorted. Based on the value in "Mach", "pocket" gets its value from one of two other fields. Here is the IIf statement.

=IIf([Mach]="6480" Or [Mach]="2624" Or [Mach]="2623",[Pocket(6480,2624)],[Pocket(6483,2625)])

This statement is in the controlsource for "pocket" and works correctly, however, it only sorts correctly when the value from Pocket(6480,2624) is inserted in the field and not when the value from Pocket(6483,2625) is inserted there.

I've gone through the Sorting and Grouping window and both fields are selected to sort with ascending. They are also in the underlying query and selected to sort with ascending.

I'm at a loss after trying several things. I'm not a VBA guy, but I'm willing to try it with a little guidance, if necessary. Have looked for code but ignorance is standing in the way of applying any.

Thanks
Kev

 

[randysmid]

Hi,
This may work better if you place the IIF statement inside the query as a new custom field that will be returned back to the report. I wrote an FAQ on this topic: faq701-3273
Please reply back if you need more help on this.

HTH,
Randy Smith
California Teachers Association

 

[toolman2]

Randy,
Thanks for your timely reply. Seems like the actual info in a reply from someone sometimes doesn't help, but it stirs up other ideas that do. That happened in this case.
After further study, I realized that my sorting is working correctly after all. The value in the field can be 1 or 2 digits. Anytime it's 1 digit I have to put a "0" in with it so it will sort right(ie: "3" vs "03&quot. Changing the value in my field fixed my problem.
Thanks again for your response. I've noticed that you are quite busy with all of the responses I've seen from you in the forum.
A great asset!

 

[randysmid]

Hi,
I hope you were able to read my FAQ, because it opens up alot of ideas for getting the results you want. Thanks for the kind comments. Be sure to check out my other FAQ's.

HTH,
Randy Smith
California Teachers Association