0
votes

This Round statement is producing an error when the IIf condition is false, it works when the IIf condition is true.

I have very limited experience with SQL. I inherited this Access DB, which I'm trying to tweak, from a colleague who has retired, so I've been taking shots in the dark at troubleshooting and piecing together syntax.

This is the Round statement I'm trying to get to work:

Round(IIf([Manual] is Not Null,[Manual]/180,[MemberDays]/180),3) AS [%ADM]

Troubleshooting edit:

Round(IIf([Manual] is Not Null,180/180,100/180),3) AS [%ADM]

The Manual field is created by this statement (not sure if relevant, but figured I'd include):

IIf(([2-billing-Prep Export]!wth_date) Is Not Null,[MemberDays]-[Session Cum]+1,"")) AS Manual

Expected results: %ADM will be populated with Manual/180 to 3 dec pts if Manual is not null. If Manual is null, %ADM will be populated with MemberDays/180 to 3 dec pts.

Actual results: The statement works if Manual is not null, but %ADM is populated with #ERROR when Manual is null. Google tells me this is because one of the fields in the calculation is null...but MemberDays is never null.

Results from troubleshooting edit: %ADM is populated as if the condition is always true, even for records where Manual is, or appears to be, null. I'm confused because before making the troubleshooting edit, the condition seemed to recognize null/non null values in Manual.

1

1 Answers

0
votes

In an IIf expression, both the true-part and the false-part are evaluated, and if one of them can't be evaluated, an error is returned. In your case, you believe that Manual can be Null sometimes, but I don't think so... because in the IIf formula for Manual, you return an empty string when it should return Null. An empty string can't be divided by a number. Therefore:

Change the expression for the Manual field to

IIf(([2-billing-Prep Export]!wth_date) Is Not Null,[MemberDays]-[Session Cum]+1,Null)) AS Manual