Calculated Fields

[Elsie]

Hi, have the following problems in Access XP:

1) we have a query as the record source of a report. supposing that we want
the value of [amount] less [gain_loss] to be computed in the report, under
the control source for a particular text box, just use:
=[amount]-[gain_loss], right? But this does not work, we observed #Error on
Access XP. However, the same report viewed on Access 2003 works fine.

2) if in a query, there are both debit and credit transactions, how do we
display them in the below format?

eg:
item: 123
debit: 1000, under account abc
credit: -1000, under account xyz

output in report:

item 123

 

[Douglas J Steele]

Not sure what the problem is in 1.

For 2, you could structure your query as:

SELECT Item, Account,
IIf(MyValue >=0, MyValue, 0) AS Debit,
IIf(MyValue <0, MyValue, 0) AS Credit
FROM MyTable

 

[adsl]

Hi, have the following problems in Access XP:

1) we have a query as the record source of a report. supposing that we
want
the value of [amount] less [gain_loss] to be computed in the report, under
the control source for a particular text box, just use:
=[amount]-[gain_loss], right? But this does not work, we observed #Error
on
Access XP. However, the same report viewed on Access 2003 works fine.

2) if in a query, there are both debit and credit transactions, how do we
display them in the below format?

eg:
item: 123
debit: 1000, under account abc
credit: -1000, under account xyz

output in report:

item 123

 

[adsl]

Not sure what the problem is in 1.

For 2, you could structure your query as:

SELECT Item, Account,
IIf(MyValue >=0, MyValue, 0) AS Debit,
IIf(MyValue <0, MyValue, 0) AS Credit
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, have the following problems in Access XP:

1) we have a query as the record source of a report. supposing that we want
the value of [amount] less [gain_loss] to be computed in the report,
under
the control source for a particular text box, just use:
=[amount]-[gain_loss], right? But this does not work, we observed #Error on
Access XP. However, the same report viewed on Access 2003 works fine.

2) if in a query, there are both debit and credit transactions, how do we
display them in the below format?

eg:
item: 123
debit: 1000, under account abc
credit: -1000, under account xyz

output in report:

item 123