IF STATEMENT

[Hendrix]

I am trying to set up a field in access that is dependent on another
field.

current field in my table are "Amt" and "D/C"

I will like to add a field that say if D/C = "C" then Amt * -1

Can some on help me with this?

 

[Klatuu]

Where are you entering the data? You will have to use a form for this and
the After Update event of the control that the D/C field is bound to. Also,
I would recommend changing the name of the D/C field. If you don't enclose
every reference to it in brackets [D/C] it will cause you problems.

 

[Hendrix]

how would I do that?

I was planning on adding a field to the new field to an existing table
and running an update query to populate it. I can't do it that way?

Where are you entering the data? You will have to use a form for this and
the After Update event of the control that the D/C field is bound to.  Also,
I would recommend changing the name of the D/C field.  If you don't enclose
every reference to it in brackets [D/C] it will cause you problems.

I am trying to set up a field in access that is dependent on another
field.

current field in my table are "Amt" and "D/C"

I will like to add a field that say if D/C = "C" then Amt * -1

Can some on help me with this?- Hide quoted text -

- Show quoted text -

 

[KARL DEWEY]

You do it in a query, not in a table.
Correct_Amt: IIF([D/C] = "C", [Amt] * -1, [Amt])

 

[KenSheridan via AccessMonster.com]

I would recommend against changing the sign of the amount if the transaction
is a credit. It leaves too much room for inconsistent data as there is
nothing to stop the values in one or other of the columns being changed
independently of each other. This arises from the redundancy inherent in
having the two columns if the sign of the Amt value is to differ for credits
and debits. You should either have the two columns and store the amounts as
positive numbers only, or remove the D/C column and have credits and debits
as differently signed values.

I would suggest that you keep the amount values as positive numbers by giving
the field a validation rule of >=0. You can reverse the sign for the credits
when shown in a form or report in a computed control with the following
expression as its ControlSource:

=[Amt]*IIf([D/C] = "C",-1,1)

When aggregating values, e.g. to get a balance Sum the same expression, e.g.
in a query to return all transactions by customer with the closing daily
balances:

SELECT [TransactionID], [CustomerID],
[TransactionDate], [D/C], [Amt],
(SELECT SUM([Amt]*IIf([D/C] = "C",-1,1))
FROM [Transactions] AS T2
WHERE T2.[CustomerID] = T1.[CustomerID]
AND T2.[TransactionDate] <= T1.[TransactionDate])
AS [Closing Balance]
FROM [Transactions] AS T1
ORDER BY [CustomerID], [TransactionDate] DESC;

An alternative approach is to have separate Credit and Debit columns in the
table, in which case the following query returns the transactions and
balances, this time per transaction rather than the closing daily balances:

SELECT CustomerID, TransactionDate, Credit, Debit,
(SELECT SUM(Credit)
FROM Transactions AS T2
WHERE T2.CustomerID = T1.CustomerID
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) -
(SELECT SUM(Debit)
FROM Transactions AS T2
WHERE T2.CustomerID = T1.CustomerID
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) AS Balance
FROM Transactions AS T1
ORDER BY CustomerID, TransactionDate DESC , TransactionID DESC;

Ken Sheridan
Stafford, England

 

[Hendrix]

Would I do this in a regular query or an update query?

You do it in a query, not in a table.
   Correct_Amt:  IIF([D/C] = "C", [Amt] * -1, [Amt])

--
Build a little, test a little.

I am trying to set up a field in access that is dependent on another
field.

current field in my table are "Amt" and "D/C"

I will like to add a field that say if D/C = "C" then Amt * -1

Can some on help me with this?
.- Hide quoted text -

- Show quoted text -

 

[KARL DEWEY]

Would I do this in a regular query or an update query?
In a regular query to be used in a report.
--
Build a little, test a little.

Would I do this in a regular query or an update query?

You do it in a query, not in a table.
Correct_Amt: IIF([D/C] = "C", [Amt] * -1, [Amt])

--
Build a little, test a little.

I am trying to set up a field in access that is dependent on another
field.

current field in my table are "Amt" and "D/C"

I will like to add a field that say if D/C = "C" then Amt * -1

Can some on help me with this?
.- Hide quoted text -

- Show quoted text -

.