Excel Formula To Access

[JHG]

I have a simple nested IF formula in excel, which I want to use in Access. I am new to Access and can not get it right although I have tried various options.


Excel Formula
=IF(B2>0,(+A2-B2),IF(B2=0,+A2))

The fields in Access in relation to the columns in Excel

Excel Column B2 = Access Field QuantityOut
Excel Column A2 = Access Field QuantityIn

[pbaldy]

The Access IIf() function has the same syntax and can be nested in the same way.

[JHG]

This one worked, but it does not do what I want to do. The problem is that One is compelled to put a figure in QuantityOut in order to get a value in StockOnHand

IIf([QuantityOut]=0,0,+[QuantityIn]-[QuantityOut])

Therefore I tried this one, which are working in Excel. I have tried to use it using the field names with the IIf formula, but it gives syntax and other errors.

[pbaldy]

How about:

IIf([QuantityOut]>0,[QuantityIn]-[QuantityOut], [QuantityIn])

which seems to approximate your Excel forumula.

[JHG]

Thank You,

I appreciate this. I am going to try it. I will let you know.

[JHG]

Thank You,

I appreciate this. IT IS WORKING PERFECTLY. I am so grateful.

[pbaldy]

Happy to help! This may also work:

[QuantityIn]-Nz([QuantityOut], 0)

[JHG]

Suddenly something went wrong.
I have tried all ways to put it in, but now it does not work anymore.
It does not want to do the calculation anymore.
I tried putting it in directly as an expr.
I tried to do it by adding a field in the table.
nothing works anymore.
Please help

[JHG]

Suddenly something went wrong.S
I have tried all ways to put it in, but now it does not work anymore.
It does not want to do the calculation anymore.
I tried putting it in directly as an expr.
I tried to do it by adding a field in the table.
nothing works anymore.


Please help

Sorry to bother you, but I used another expression which works as well, but not as well as the one you gave me.
[QuantityIn]-[QuantityOut]

[pbaldy]

What syntax is failing? Can you attach the db here?

[JHG]

Ok, here I tried to zip with save as on save and publish, but it keeps doing this error. Is there any other way to send the file perhaps?

[JHG]

​I am doing the following to add the code.

IIf
Choose Table
Data Sheet View
Click “Click to add” on the fields
Choose Calculated Field
Choose Number
Paste IIf([QuantityOut]=0,0,+[QuantityIn]-[QuantityOut]) with “Ctrl V”

[pbaldy]

You'd want to attach an accdb, not an accde. May help to zip it first as well.

https://www.accessforums.net/showthread.php?t=70301

[JHG]

I thought the idea of save the file to a accde was to zip it. If not can I use winrar to zip it.

[JHG]

Ok here is a zip file of the accdb file.

Turnkey Stock (2).zip