How do I display either of two fields in Report column.

[John]

I have two tables Invoices and Payments. I want to design a statement report
with data from both tables sorted by date.
Column headings are:
DATE INVOICE NUMBER DETAIL (supply or payment) AMOUNT (either
invoice amount or payment amount.).

I need to get all the payment records and all the invoice records for a
particular month and display each one sorted by date.
So the date field and the amount fields will have data from both tables,
depending on the date. There will be a separate report for each customer.
How do I get the report to do this.

Any help appreciated

John

[Wayne Morgan]

Can you tell which of the two it is other than by the name of the field the
value is in? If so, use an alias for the field name in one of the cases so
that the output field name is the same regardless if the value is an invoice
or payment. In the textbox, use an IIF statement or conditional formatting
based on the value of this other field to format the value as positive or
negative.

If you have to use the field name to tell, then use an IIF statement in the
textbox to return whichever field has a value.

Example:
=IIf(IsNull([InvoiceAmt]), [PaymentAmt], [InvoiceAmt])

You could add formatting to each one so that they appear differently in the
report.

--
Wayne Morgan
MS Access MVP
"John" <br****@gofree.indigo.ie> wrote in message
news:cr**********@kermit.esat.net...

I have two tables Invoices and Payments. I want to design a statement
report with data from both tables sorted by date.
Column headings are:
DATE INVOICE NUMBER DETAIL (supply or payment) AMOUNT (either
invoice amount or payment amount.).

I need to get all the payment records and all the invoice records for a
particular month and display each one sorted by date.
So the date field and the amount fields will have data from both tables,
depending on the date. There will be a separate report for each customer.
How do I get the report to do this.

Any help appreciated

John

[John]

Thanks Wayne, that'll do the job.
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:2K******************@newssvr11.news.prodigy.c om...

Can you tell which of the two it is other than by the name of the field
the value is in? If so, use an alias for the field name in one of the
cases so that the output field name is the same regardless if the value is
an invoice or payment. In the textbox, use an IIF statement or conditional
formatting based on the value of this other field to format the value as
positive or negative.

If you have to use the field name to tell, then use an IIF statement in
the textbox to return whichever field has a value.

Example:
=IIf(IsNull([InvoiceAmt]), [PaymentAmt], [InvoiceAmt])

You could add formatting to each one so that they appear differently in
the report.

--
Wayne Morgan
MS Access MVP
"John" <br****@gofree.indigo.ie> wrote in message
news:cr**********@kermit.esat.net...

I have two tables Invoices and Payments. I want to design a statement
report with data from both tables sorted by date.
Column headings are:
DATE INVOICE NUMBER DETAIL (supply or payment) AMOUNT (either
invoice amount or payment amount.).

I need to get all the payment records and all the invoice records for a
particular month and display each one sorted by date.
So the date field and the amount fields will have data from both tables,
depending on the date. There will be a separate report for each customer.
How do I get the report to do this.

Any help appreciated

John