If, Then calculation in a report

[OtterCubDC]

Hello professionals - I hope you can help me solve a problem that's
been haunting me with a database I'm building.

The two tables I'm working from are tblEvents and tblIncome. The
Income table is used to store data entered in a subform. (In other
words, when someone is working on details in an Event form, they enter
batches of income into the Income subform section.)

Within the Income table, I have the following relevant fields:

EventID (auto-generated in the Events table)
GeneralLedgerLine (this is a pull-down list)
Amount

The report I'm working on is a basic budget form that lists both the
budgeted amounts for each GL line (pulled directly from fields in the
Events table), but would list actual deposited income in the next
column, again broken out by GL line.

So the basic expression I'm trying to build is one that will go look
at the Income table, and sum all batches where the GL line is a
certain value. If I wrote it out in English, I might say:

"Sum up the Amount for all records related to this Event where
GeneralLedgerLine = "Ticket Sales"."

I've tried using the IIF function, but can't seem to get it to work
properly.

Can anyone offer me some insight on how best to pull this information?

 

[Marshall Barton]

Hello professionals - I hope you can help me solve a problem that's
been haunting me with a database I'm building.

The two tables I'm working from are tblEvents and tblIncome. The
Income table is used to store data entered in a subform. (In other
words, when someone is working on details in an Event form, they enter
batches of income into the Income subform section.)

Within the Income table, I have the following relevant fields:

EventID (auto-generated in the Events table)
GeneralLedgerLine (this is a pull-down list)
Amount

The report I'm working on is a basic budget form that lists both the
budgeted amounts for each GL line (pulled directly from fields in the
Events table), but would list actual deposited income in the next
column, again broken out by GL line.

So the basic expression I'm trying to build is one that will go look
at the Income table, and sum all batches where the GL line is a
certain value. If I wrote it out in English, I might say:

"Sum up the Amount for all records related to this Event where
GeneralLedgerLine = "Ticket Sales"."

I've tried using the IIF function, but can't seem to get it to work
properly.

Use a text box expression like:
=Sum(IIf([GL] = "Ticket Sales", Amount, -))

If you want more than a couple of this type of total, you
should seriously consider using a subreport based on a query
that calculates all of them"

SELECT [GL], Sum(Amount) As GLtotal
FROM reportquery
GROUP BY [GL]

 

[OtterCubDC]

Hello professionals - I hope you can help me solve a problem that's
been haunting me with a database I'm building.

The two tables I'm working from are tblEvents and tblIncome. The
Income table is used to store data entered in a subform. (In other
words, when someone is working on details in an Event form, they enter
batches of income into the Income subform section.)

Within the Income table, I have the following relevant fields:

EventID (auto-generated in the Events table)
GeneralLedgerLine (this is a pull-down list)
Amount

The report I'm working on is a basic budget form that lists both the
budgeted amounts for each GL line (pulled directly from fields in the
Events table), but would list actual deposited income in the next
column, again broken out by GL line.

So the basic expression I'm trying to build is one that will go look
at the Income table, and sum all batches where the GL line is a
certain value. If I wrote it out in English, I might say:

"Sum up the Amount for all records related to this Event where
GeneralLedgerLine = "Ticket Sales"."

I've tried using the IIF function, but can't seem to get it to work
properly.

Use a text box expression like:
=Sum(IIf([GL] = "Ticket Sales", Amount, -))

If you want more than a couple of this type of total, you
should seriously consider using a subreport based on a query
that calculates all of them"

SELECT [GL], Sum(Amount) As GLtotal
FROM reportquery
GROUP BY [GL]

When I use the expression you mention above, I get this error message:

"The expression you entered has too many closing parantheses."

If I try removing any of the parantheses, I get other errors.

 

[Marshall Barton]

Hello professionals - I hope you can help me solve a problem that's
been haunting me with a database I'm building.

The two tables I'm working from are tblEvents and tblIncome. The
Income table is used to store data entered in a subform. (In other
words, when someone is working on details in an Event form, they enter
batches of income into the Income subform section.)

Within the Income table, I have the following relevant fields:

EventID (auto-generated in the Events table)
GeneralLedgerLine (this is a pull-down list)
Amount

The report I'm working on is a basic budget form that lists both the
budgeted amounts for each GL line (pulled directly from fields in the
Events table), but would list actual deposited income in the next
column, again broken out by GL line.

So the basic expression I'm trying to build is one that will go look
at the Income table, and sum all batches where the GL line is a
certain value. If I wrote it out in English, I might say:

"Sum up the Amount for all records related to this Event where
GeneralLedgerLine = "Ticket Sales"."

I've tried using the IIF function, but can't seem to get it to work
properly.

Use a text box expression like:
=Sum(IIf([GL] = "Ticket Sales", Amount, -))

If you want more than a couple of this type of total, you
should seriously consider using a subreport based on a query
that calculates all of them"

SELECT [GL], Sum(Amount) As GLtotal
FROM reportquery
GROUP BY [GL]

When I use the expression you mention above, I get this error message:

"The expression you entered has too many closing parantheses."

Fix my typo by changing the - to 0.

You also need to review each name that I used and change
them to the ones you have in the report's record source
query.