Ordering dates in a report

[Jim]

Each month we receive payments from clients and then print a simple Access
report listing each clients name, payment amount, and day of the month the
payment was made as shown below.

Client Amount Payment Date
Brown $127.34 28
Davis $78.92 2
Jones 253.16 10
Martin 92.15 7

Beneath the Payment Date, which is just a label within the report body, is a
rather complex iif statement which extracts these dates from my database
fields. A friend of mine added this some time ago so I'm at a loss to
explain how it works.

=iif(DatePart("m",[Pay1]=Enter
Month]),DatePart("d",....................................

What I would like to do revise the report so it will list the payments in
order by date, 2nd, 7th, 10th etc. for the example shown. Is this possible?

Jim

 

[Douglas J. Steele]

Reports have a Sorting and Grouping dialog that controls the order of the
records on the report.

Open the report in Design view. If you can't find the button for the Sorting
and Grouping dialog, look under View on the menu.

 

[Jim]

If I try to enter the complex iif expression into the Sorting and Grouping
box, it tells me the expression is too complicated to evaulate.

Jim

Reports have a Sorting and Grouping dialog that controls the order of the
records on the report.

Open the report in Design view. If you can't find the button for the
Sorting and Grouping dialog, look under View on the menu.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Each month we receive payments from clients and then print a simple
Access report listing each clients name, payment amount, and day of the
month the payment was made as shown below.

Client Amount Payment Date
Brown $127.34 28
Davis $78.92 2
Jones 253.16 10
Martin 92.15 7

Beneath the Payment Date, which is just a label within the report body,
is a rather complex iif statement which extracts these dates from my
database fields. A friend of mine added this some time ago so I'm at a
loss to explain how it works.

=iif(DatePart("m",[Pay1]=Enter
Month]),DatePart("d",....................................

What I would like to do revise the report so it will list the payments in
order by date, 2nd, 7th, 10th etc. for the example shown. Is this
possible?

Jim

 

[Douglas J. Steele]

Without knowing what your IIf statement is doing (you haven't shown the
whole thing...), I can't really comment whether or not you need it in the
Sorting and Grouping dialog. My suspicion is that you don't need it: that
all you need is the Payment Date, and that the IIf statement is simply used
to determine which records will be in the recordset.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

If I try to enter the complex iif expression into the Sorting and Grouping
box, it tells me the expression is too complicated to evaulate.

Jim

Reports have a Sorting and Grouping dialog that controls the order of the
records on the report.

Open the report in Design view. If you can't find the button for the
Sorting and Grouping dialog, look under View on the menu.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Each month we receive payments from clients and then print a simple
Access report listing each clients name, payment amount, and day of the
month the payment was made as shown below.

Client Amount Payment Date
Brown $127.34 28
Davis $78.92 2
Jones 253.16 10
Martin 92.15 7

Beneath the Payment Date, which is just a label within the report body,
is a rather complex iif statement which extracts these dates from my
database fields. A friend of mine added this some time ago so I'm at a
loss to explain how it works.

=iif(DatePart("m",[Pay1]=Enter
Month]),DatePart("d",....................................

What I would like to do revise the report so it will list the payments
in order by date, 2nd, 7th, 10th etc. for the example shown. Is this
possible?

Jim

 

[Jim]

Client payments are made on either a quarterly or monthly basis. Date fields
PAY1 through PAY4 represent the four quarters. The following iif expression
extracts the actual monthly payment date which is then printed in the
report. Payment Date is just a text heading for the report. Someone gave me
this expression and I must admit I don't quite understand how it works - but
it does.

I've been using this database and report for several years and everything
works just fine. Now the report is getting rather long and it would be nice
if the Client payment dates were listed in order. To run the query and
generate the report, I simple enter the month number (1 for Jan, 2 for Feb,
etc). That's why the [enter month] in the expression below.

=iif(datepart("m"),[pay1]=[enter
month]),datepart("d",[pay1]),iif(datepart("m",[pay2]=[enter
month]),"n2",iif(datepart("m",[pay3]=[enter
month]),"n3",iif(datepart("m",[pay4]=[enter month]),"n4"))))

Jim

Without knowing what your IIf statement is doing (you haven't shown the
whole thing...), I can't really comment whether or not you need it in the
Sorting and Grouping dialog. My suspicion is that you don't need it: that
all you need is the Payment Date, and that the IIf statement is simply
used to determine which records will be in the recordset.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

If I try to enter the complex iif expression into the Sorting and
Grouping box, it tells me the expression is too complicated to evaulate.

Jim

Reports have a Sorting and Grouping dialog that controls the order of
the records on the report.

Open the report in Design view. If you can't find the button for the
Sorting and Grouping dialog, look under View on the menu.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Each month we receive payments from clients and then print a simple
Access report listing each clients name, payment amount, and day of the
month the payment was made as shown below.

Client Amount Payment Date
Brown $127.34 28
Davis $78.92 2
Jones 253.16 10
Martin 92.15 7

Beneath the Payment Date, which is just a label within the report body,
is a rather complex iif statement which extracts these dates from my
database fields. A friend of mine added this some time ago so I'm at a
loss to explain how it works.

=iif(DatePart("m",[Pay1]=Enter
Month]),DatePart("d",....................................

What I would like to do revise the report so it will list the payments
in order by date, 2nd, 7th, 10th etc. for the example shown. Is this
possible?

Jim

 

[Jim]

Follow up

If anyone can suggest a simpler method for extracting and ordering these
payment dates, please let me know.

Jim

Client payments are made on either a quarterly or monthly basis. Date
fields PAY1 through PAY4 represent the four quarters. The following iif
expression extracts the actual monthly payment date which is then printed
in the report. Payment Date is just a text heading for the report. Someone
gave me this expression and I must admit I don't quite understand how it
works - but it does.

I've been using this database and report for several years and everything
works just fine. Now the report is getting rather long and it would be
nice if the Client payment dates were listed in order. To run the query
and generate the report, I simple enter the month number (1 for Jan, 2 for
Feb, etc). That's why the [enter month] in the expression below.

=iif(datepart("m"),[pay1]=[enter
month]),datepart("d",[pay1]),iif(datepart("m",[pay2]=[enter
month]),"n2",iif(datepart("m",[pay3]=[enter
month]),"n3",iif(datepart("m",[pay4]=[enter month]),"n4"))))

Jim

Without knowing what your IIf statement is doing (you haven't shown the
whole thing...), I can't really comment whether or not you need it in the
Sorting and Grouping dialog. My suspicion is that you don't need it: that
all you need is the Payment Date, and that the IIf statement is simply
used to determine which records will be in the recordset.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

If I try to enter the complex iif expression into the Sorting and
Grouping box, it tells me the expression is too complicated to evaulate.

Jim

Reports have a Sorting and Grouping dialog that controls the order of
the records on the report.

Open the report in Design view. If you can't find the button for the
Sorting and Grouping dialog, look under View on the menu.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Each month we receive payments from clients and then print a simple
Access report listing each clients name, payment amount, and day of
the month the payment was made as shown below.

Client Amount Payment Date
Brown $127.34 28
Davis $78.92 2
Jones 253.16 10
Martin 92.15 7

Beneath the Payment Date, which is just a label within the report
body, is a rather complex iif statement which extracts these dates
from my database fields. A friend of mine added this some time ago so
I'm at a loss to explain how it works.

=iif(DatePart("m",[Pay1]=Enter
Month]),DatePart("d",....................................

What I would like to do revise the report so it will list the payments
in order by date, 2nd, 7th, 10th etc. for the example shown. Is this
possible?

Jim

 

[Duane Hookom]

There is no way that your expression should work that I can identify. It's
hard to believe you copied the expression directly from your control source
since DatePart() should be proper-cased and the function requires at least 2
arguments and your first datepart() has only 1. IIf() expects 3 arguments
and your first has only 2. Also, your ()s don't balance.

I don't agree with having 4 date fields PAY1-PAY4 but they might be working
for you (with some complications).

--
Duane Hookom
MS Access MVP

Client payments are made on either a quarterly or monthly basis. Date
fields PAY1 through PAY4 represent the four quarters. The following iif
expression extracts the actual monthly payment date which is then printed
in the report. Payment Date is just a text heading for the report. Someone
gave me this expression and I must admit I don't quite understand how it
works - but it does.

I've been using this database and report for several years and everything
works just fine. Now the report is getting rather long and it would be
nice if the Client payment dates were listed in order. To run the query
and generate the report, I simple enter the month number (1 for Jan, 2 for
Feb, etc). That's why the [enter month] in the expression below.

=iif(datepart("m"),[pay1]=[enter
month]),datepart("d",[pay1]),iif(datepart("m",[pay2]=[enter
month]),"n2",iif(datepart("m",[pay3]=[enter
month]),"n3",iif(datepart("m",[pay4]=[enter month]),"n4"))))

Jim

Without knowing what your IIf statement is doing (you haven't shown the
whole thing...), I can't really comment whether or not you need it in the
Sorting and Grouping dialog. My suspicion is that you don't need it: that
all you need is the Payment Date, and that the IIf statement is simply
used to determine which records will be in the recordset.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

If I try to enter the complex iif expression into the Sorting and
Grouping box, it tells me the expression is too complicated to evaulate.

Jim

Reports have a Sorting and Grouping dialog that controls the order of
the records on the report.

Open the report in Design view. If you can't find the button for the
Sorting and Grouping dialog, look under View on the menu.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Each month we receive payments from clients and then print a simple
Access report listing each clients name, payment amount, and day of
the month the payment was made as shown below.

Client Amount Payment Date
Brown $127.34 28
Davis $78.92 2
Jones 253.16 10
Martin 92.15 7

Beneath the Payment Date, which is just a label within the report
body, is a rather complex iif statement which extracts these dates
from my database fields. A friend of mine added this some time ago so
I'm at a loss to explain how it works.

=iif(DatePart("m",[Pay1]=Enter
Month]),DatePart("d",....................................

What I would like to do revise the report so it will list the payments
in order by date, 2nd, 7th, 10th etc. for the example shown. Is this
possible?

Jim

 

[Jim]

I didn't copy the expression, I re-typed it, probably with an error or two.
And yes, it does work.

I was able to solve my problem by adding a new field (PAYDATE) and making a
few other minor changes to the database - thanks to all who responded.

Jim

There is no way that your expression should work that I can identify. It's
hard to believe you copied the expression directly from your control
source since DatePart() should be proper-cased and the function requires
at least 2 arguments and your first datepart() has only 1. IIf() expects 3
arguments and your first has only 2. Also, your ()s don't balance.

I don't agree with having 4 date fields PAY1-PAY4 but they might be
working for you (with some complications).

--
Duane Hookom
MS Access MVP

Client payments are made on either a quarterly or monthly basis. Date
fields PAY1 through PAY4 represent the four quarters. The following iif
expression extracts the actual monthly payment date which is then printed
in the report. Payment Date is just a text heading for the report.
Someone gave me this expression and I must admit I don't quite understand
how it works - but it does.

I've been using this database and report for several years and everything
works just fine. Now the report is getting rather long and it would be
nice if the Client payment dates were listed in order. To run the query
and generate the report, I simple enter the month number (1 for Jan, 2
for Feb, etc). That's why the [enter month] in the expression below.

=iif(datepart("m"),[pay1]=[enter
month]),datepart("d",[pay1]),iif(datepart("m",[pay2]=[enter
month]),"n2",iif(datepart("m",[pay3]=[enter
month]),"n3",iif(datepart("m",[pay4]=[enter month]),"n4"))))

Jim

Without knowing what your IIf statement is doing (you haven't shown the
whole thing...), I can't really comment whether or not you need it in
the Sorting and Grouping dialog. My suspicion is that you don't need it:
that all you need is the Payment Date, and that the IIf statement is
simply used to determine which records will be in the recordset.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


If I try to enter the complex iif expression into the Sorting and
Grouping box, it tells me the expression is too complicated to
evaulate.

Jim

message Reports have a Sorting and Grouping dialog that controls the order of
the records on the report.

Open the report in Design view. If you can't find the button for the
Sorting and Grouping dialog, look under View on the menu.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Each month we receive payments from clients and then print a simple
Access report listing each clients name, payment amount, and day of
the month the payment was made as shown below.

Client Amount Payment Date
Brown $127.34 28
Davis $78.92 2
Jones 253.16 10
Martin 92.15 7

Beneath the Payment Date, which is just a label within the report
body, is a rather complex iif statement which extracts these dates
from my database fields. A friend of mine added this some time ago so
I'm at a loss to explain how it works.

=iif(DatePart("m",[Pay1]=Enter
Month]),DatePart("d",....................................

What I would like to do revise the report so it will list the
payments in order by date, 2nd, 7th, 10th etc. for the example shown.
Is this possible?

Jim

 

[Douglas J Steele]

Put the IIf statement in the underlying query, rather than in the form.
You'll then have a single value you can sort on.

(You would, of course, probably be better off normalizing your database so
that you don't have the 4 separate Pay fields.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Follow up

If anyone can suggest a simpler method for extracting and ordering these
payment dates, please let me know.

Jim

Client payments are made on either a quarterly or monthly basis. Date
fields PAY1 through PAY4 represent the four quarters. The following iif
expression extracts the actual monthly payment date which is then printed
in the report. Payment Date is just a text heading for the report. Someone
gave me this expression and I must admit I don't quite understand how it
works - but it does.

I've been using this database and report for several years and everything
works just fine. Now the report is getting rather long and it would be
nice if the Client payment dates were listed in order. To run the query
and generate the report, I simple enter the month number (1 for Jan, 2 for
Feb, etc). That's why the [enter month] in the expression below.

=iif(datepart("m"),[pay1]=[enter
month]),datepart("d",[pay1]),iif(datepart("m",[pay2]=[enter
month]),"n2",iif(datepart("m",[pay3]=[enter
month]),"n3",iif(datepart("m",[pay4]=[enter month]),"n4"))))

Jim

Without knowing what your IIf statement is doing (you haven't shown the
whole thing...), I can't really comment whether or not you need it in the
Sorting and Grouping dialog. My suspicion is that you don't need it: that
all you need is the Payment Date, and that the IIf statement is simply
used to determine which records will be in the recordset.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


If I try to enter the complex iif expression into the Sorting and
Grouping box, it tells me the expression is too complicated to evaulate.

Jim

Reports have a Sorting and Grouping dialog that controls the order of
the records on the report.

Open the report in Design view. If you can't find the button for the
Sorting and Grouping dialog, look under View on the menu.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Each month we receive payments from clients and then print a simple
Access report listing each clients name, payment amount, and day of
the month the payment was made as shown below.

Client Amount Payment Date
Brown $127.34 28
Davis $78.92 2
Jones 253.16 10
Martin 92.15 7

Beneath the Payment Date, which is just a label within the report
body, is a rather complex iif statement which extracts these dates
from my database fields. A friend of mine added this some time ago so
I'm at a loss to explain how it works.

=iif(DatePart("m",[Pay1]=Enter
Month]),DatePart("d",....................................

What I would like to do revise the report so it will list the payments
in order by date, 2nd, 7th, 10th etc. for the example shown. Is this
possible?

Jim