adding a null value

[fishhead]

Can anyone suggestion how i can add a null value as follows:



[pymt] = payment amount
[woamt] = write off amount.

my query has both these fields and i wish to add them together to get a total amount of the [pymt] + [woamt] even if the [woamt] is null.

Sometimes we have a value to write off and others we don't.

In the query i am creating a field: " x: ([pymt]+[woamt])"
but this returns a null value if the [woamt] amount is null. If there is a value in the [woamt] field then it sums the two values together to give a total otherwise the new [x] field remains blank.

Is there a way to have the [x] field return the [pymt] value if the [woamt] field is blank?

thanks in advance.

[kd2017]

x: pymt + nz(woamt, 0)

OR

x: pymt + iif(woamt is null, 0, woamt)

See https://www.fmsinc.com/MicrosoftAccess/vba/nz.htm
and http://allenbrowne.com/QueryPerfIssue.html#IsNull

[ArviLaanemets]

You have different types of amount (all of them actually same type of data - monetary amount of different financial transactions) in separate fields. I myself avoid such design even in Excel apps. In access, this is a big NO!

You need a table e.g. tblTransactionTypes: TransactionType, TypeInfo
with values like
TransactionType, TypeInfo
1 payment
2 write off
...

You need a table tblTransactions: TransactID, TransactDate, TransactType, TransactAmount, ...

Your query will be like

SELECT ..., SUM(TransactAmount) WHERE ... AND TransactType IN (1,2) GROUP BY ...

Such design assumes, that all incoming amounts are entered as positive numbers, and outgoing amounts as negative numbers.

When all movements for certain transaction type are always or mostly in single direction , you can modify the design:
tblTransactionTypes: TransactType, TypeInfo, TypeDirection (where [mostly] TypeDirection = 1 for incoming types and -1 for outgoing types).

Your query will then be like

SELECT ..., SUM(trans.TransactAmount*tt.TypeDirection) FROM tblTransactions trans INNER JOIN tblTransactionTypes tt ON tt.TransactType = trans.TransactType WHERE ... AND trans.TransactType IN (1,2) GROUP BY ...

Now all normal amounts are as positive numbers, negative amounts may be restricted or are used as exception - depending how transaction types are defined. E.g. tblTransactionTypes has an additional field like NegativeAllowed with possible values 0 (user can enter only positive amounts) or 1 (user can enter positive and negative amounts)

All designs described here allow your add new transaction types in future without redesigning database every time you do this.