sorting lines on reports

[Guest]

I am working on a database which formats output files from the mainframe and
translates the coding and billing information into a customer-friendly
statement. We have 5 statement types: Type R, Type O, Installments, Type Z,
and Type MK. Z and MK are special formats of R and O, respectively, for
special accounts. (Both of these have to do with accounts brought in from
store chains our company has bought out and converted to our billing system.)
On all of these statments, there has to be a beginning line when the account
was opened, and on the Type O statement, a beginning balance. (Type R
statements are recurring rental accounts which have no beginning balance.) My
problem is this:

Access is currently sorting the data based on date, then transaction type,
as follows:

9/14/01 Monthly Billing 02
9/15/01 Payment
10/15/01 Monthly Billing 03

and so on. However, the beginning of the statement looks like this:

04/15/01 Beginning Balance
04/15/01 Down Payment
04/15/01 New Account Opened

Which makes no sense, why would the beginning balance and the down payment
be listed before the account has been opened? Is there a way to force Access
to format the first 3 lines of the report a certain way, then allow the rest
to be formatted as they normally would? Or at least force the report to show
the New Account Opened line first?

 

[Duane Hookom]

It looks like "B" comes before "D" and "N" in the reports which makes sense.
If you want to sort the report in some other order, you should create a
small lookup table with fields and values like:

1 New Account Opened
2 Beginning Balance
3 Down Payment
4 ....

You can add this table to your report's record source and join the
appropriate fields. Add the 1,2,3.. field to the grid so you can use it in
the report for sorting.

 

[Guest]

I know the report is sorting alphabetically... this is fine for everything
but the first three lines. As far as a lookup table, how do I go about
creating a lookup table to account for every line in the statement when each
statement has a different number of lines? Or does the lookup table just need
to handle the first three? I'm confused on that point.

 

[Duane Hookom]

Are there lots of possible value such as New Account.. and Down Payment....?
If so, you can use an expression in the sorting and grouping like:

=100-Instr("Text for Third Line...Text for Second Line...Text For First
Line", [YourFieldName])

 

[Guest]

Ok, I think I understand that... I'll try that and see if it works.

Are there lots of possible value such as New Account.. and Down Payment....?
If so, you can use an expression in the sorting and grouping like:

=100-Instr("Text for Third Line...Text for Second Line...Text For First
Line", [YourFieldName])

--
Duane Hookom
MS Access MVP
--

I know the report is sorting alphabetically... this is fine for everything
but the first three lines. As far as a lookup table, how do I go about
creating a lookup table to account for every line in the statement when
each
statement has a different number of lines? Or does the lookup table just
need
to handle the first three? I'm confused on that point.

 

[Guest]

Well... That almost works. It sorts the three out at the top, just like I
need 'em. The only problem now is everything that comes after it is sorted in
alphabetical order instead of by date order. So I have a block of all the
payments made on the account, then a block of all the monthly billings, and a
block of all the late charges. lol.


Is there a way to sort this by date, then by transaction type, and still
keep the three at the top the same?

 

[Duane Hookom]

Did you include a sorting by date following the sorting by the expression
that I provided? Or, did you rely on chance for sorting beyond my expression
;-)

 

[Guest]

I did both actually. At first I thought it might catch them by date, so I let
it sort on it's own, with no success whatsoever. Then I added a date sort,
but it didn't work right. It sorted Payments by date, then it sorted the rest
of the report by date like I wanted... but I can't figure out why it didn't
sort the Payments correctly. It looked something like this:

09/14/01 New Account Opened
09/14/01 Beginning Balance
09/14/01 Down Payment
10/30/01 Payment 1
11/31/01 Payment 2
12/31/01 Payment 3
etc...
10/14/01 Monthly Billing 1
10/27/01 Late Charge
11/14/01 Monthly Billing 2
11/27/01 Late Charge
etc...

Why would it be sorting the payments outside of the date sort on the
billings and late charges?

 

[Duane Hookom]

Because the word "Payment" is found inside "Down Payment".
Try
=100-Instr("~Text for Third Line~...~Text for Second Line~...~Text For First
Line~", "~" &[YourFieldName] & "~")

 

[Guest]

That nailed it, thanks man Yay me, hehe.

Because the word "Payment" is found inside "Down Payment".
Try
=100-Instr("~Text for Third Line~...~Text for Second Line~...~Text For First
Line~", "~" &[YourFieldName] & "~")


--
Duane Hookom
MS Access MVP
--

I did both actually. At first I thought it might catch them by date, so I
let
it sort on it's own, with no success whatsoever. Then I added a date sort,
but it didn't work right. It sorted Payments by date, then it sorted the
rest
of the report by date like I wanted... but I can't figure out why it
didn't
sort the Payments correctly. It looked something like this:

09/14/01 New Account Opened
09/14/01 Beginning Balance
09/14/01 Down Payment
10/30/01 Payment 1
11/31/01 Payment 2
12/31/01 Payment 3
etc...
10/14/01 Monthly Billing 1
10/27/01 Late Charge
11/14/01 Monthly Billing 2
11/27/01 Late Charge
etc...

Why would it be sorting the payments outside of the date sort on the
billings and late charges?