Why does my calculation work for everything except when dealling with numbers < 1

[skrobe2]

Am building an Access 2016 db for personal use for my husband's and my credit card and checking account expenses. So far, it works as expected for everything except one specific kind of instance.

I have worked with relational db's quite a bit, but not access, so I am a bit new to this.

I have a calculated field where the result type is currency and the format is currency. My calculation is this: IIf([Full Amt]*[Div Amt] And IsNull([Sandy Amt]),[Full Amt]*[Div Amt],[Sandy Amt]).

I have a situation where the Div Amt = 0.50, and my Full Amt = .33. I get no results from my calculation. Since all the other instances in my table have all involved numbers where Full Amt is greater than 1.00, I am sure that the problem is that .33 is less that 1.00. However, I am not making any sense as to why this is a problem.



I must have defined something incorrectly, but am not sure what nor what it should be defined as.

Thank you for your input. Feeling a bit silly now...

[RuralGuy]

You should really do calculations in Queries and not have calculated values in fields.

[skrobe2]

So if I put this calculation into a query, would that also resolve my issue?

[RuralGuy]

Probably not but at least you would not have a potential bogus value in your table. Have you tried displaying all of the values with a Debug.Print? You are probably dealing with a Null value that Nz() could probably resolve.

[skrobe2]

Thank you for your reply. As it turns out, my husband wants us to use Excel, and so we are going that direction. I liked Access because of the ease of querying reports; but it really does not do exactly what we need. So we have moved in a different direction for this project.

[RuralGuy]

That's fine. If you ever change your mind just start a new post. I'll mark this as Solved for now.