Expression Description

[Ron A.]

I have this expression in a query of a sample database downloaded from
Microsoft and was wondering if someone could help explain what it does. Also,
what does using the exclamation between fields do?

Actual Amount: IIf([Categories].[Income/Expense]="Expense",-([Account
Transactions]![Transaction Amount]),[Account Transactions]![Transaction
Amount])

 

[Gina Whipp]

Actual Amount:

1. IIf([Categories].[Income/Expense]="Expense",-([Account
Transactions]![Transaction Amount]),[Account Transactions]![Transaction
Amount])

If ( [TableName].[FieldName] = "FieldValue" Then (that would be the comma)

Show me [TableName].[FieldName] Else (that would be the second comma)

Show me [TableName].[FieldName]

For the difference between a Bang (!) and Dot (.) (much discussed) see...

http://rogersaccessblog.blogspot.com/2009/04/bang-vs-dot-in-dao.html

http://bytes.com/topic/access/answers/603356-difference-between-dot-bang-notation

http://blogs.msdn.com/access/archive/2008/05/30/dot-or-bang.aspx

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Ken Snell MVP]

The expression causes an "expense" item to be shown as a negative number and
an "income" item to be shown as a positive number.

The ! is really not the right operator to use... it should be . operator.

Actual Amount: IIf([Categories].[Income/Expense]="Expense",-([Account
Transactions].[Transaction Amount]),[Account Transactions].[Transaction
Amount])

 

[Jerry Whittle]

In English: If the [Income/Expense] field has "Expense" in it, make the
[Transaction Amount] a negative number. Otherwise leave it as is. The value
will show up as Actual Amount in a query.

For the most part, the bang ( ! ) and splat (* ) can be used interchangeably
in things like below.

 

[Ron A.]

Thanks all for the explanation.
--
Ron

The expression causes an "expense" item to be shown as a negative number and
an "income" item to be shown as a positive number.

The ! is really not the right operator to use... it should be . operator.

Actual Amount: IIf([Categories].[Income/Expense]="Expense",-([Account
Transactions].[Transaction Amount]),[Account Transactions].[Transaction
Amount])

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have this expression in a query of a sample database downloaded from
Microsoft and was wondering if someone could help explain what it does.
Also,
what does using the exclamation between fields do?

Actual Amount: IIf([Categories].[Income/Expense]="Expense",-([Account
Transactions]![Transaction Amount]),[Account Transactions]![Transaction
Amount])