report sorting formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mpmason19 - 29 May 2007 20:33 GMT

Hi everyone -
awhile back i posted on here a question about sorting a report in a
"weird" way. i have a slightly different question now...

i have a report that is sorted by a purchase order number (ponum) from
most recent to oldest. currently we have ponums from 1-2999, but are
starting to get ponums of 3000+.

this is the formula:
=IIf(CInt(Left([po_number],2))>=30,"19" & [po_number],"20" &
[po_number])

it works to sort from 2999 (most recent) - oldest perfectly, except
now i have to add in the 3000's on top on the report

thanks

Reply to this Message

Wayne-I-M - 29 May 2007 20:49 GMT

Hi

It may seem 2 daft questions but ....

If ponums are numbers why not simply sort them desending
and
Not sure what you are doing with this (I know what it will do, I just don't
know why you want it to do this).
=IIf(CInt(Left([po_number],2))>=30,"19" & [po_number],"20" & [po_number])

Bit more info please would help

Signature

Wayne
Manchester, England.

> Hi everyone -
> awhile back i posted on here a question about sorting a report in a
[quoted text clipped - 12 lines]
>
> thanks

Reply to this Message

mpmason19 - 29 May 2007 21:10 GMT

On May 29, 3:49 pm, Wayne-I-M <Wayn...@discussions.microsoft.com>
wrote:

> Hi
>
[quoted text clipped - 28 lines]
>
> > thanks

the reason i have to sort it like that is for quick reference - so
anyone who wants to look for a certain ponum (ie. company president,
secretary, etc.) can search through a printout quick

thanks for the feedback

Reply to this Message

mpmason19 - 29 May 2007 20:52 GMT

> Hi everyone -
> awhile back i posted on here a question about sorting a report in a
[quoted text clipped - 12 lines]
>
> thanks

PROBLEM SOLVED
i was able to get the 3000s on top by changing part of the forumla:
=IIf(CInt(Left([po_number],2))>=***90***,"19" & [po_number],"20" &
[po_number])

the ***90*** was changed from 30, which was the max amount in the
po_number field, and the ponums were starting at 30xx

Reply to this Message

Wayne-I-M - 29 May 2007 21:41 GMT

Still seems a bit strange to me - but I don't know your DB. I may just sort
by number desending and put an unbound text box on the form called
FindThePonum

Private Sub FindThePonum_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ponum] = " & Str(Nz(Me![FindThePonum], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This should keep the company president, secretary, etc happy with the speed
they can go to any record.

Signature

Wayne
Manchester, England.

> > Hi everyone -
> > awhile back i posted on here a question about sorting a report in a
[quoted text clipped - 20 lines]
> the ***90*** was changed from 30, which was the max amount in the
> po_number field, and the ponums were starting at 30xx

Reply to this Message