• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

Access Report: Iff Statement

I have the following code on a txt box:  =IIf([NovPayStatus]=0 And [NovPayment]<>0,[NovPayment],"0")

It works for the first record of my report but returns 0 for records after the 1st one.    Any ideas on what I could possibly be doing wrong?  

1 Solution
Rey Obrero (Capricorn1)Commented:
Instead of placing the code in the control source of the textbox,

create a query that include the expression, IIf([NovPayStatus]=0 And [NovPayment]<>0,[NovPayment],"0")

something like this

select *, IIf([NovPayStatus]=0 And [NovPayment]<>0,[NovPayment],"0") as Payment
from TableX

use the query as the Record Source of the report
then assign fields from the query as  Control Source of the textboxes
Jeffrey CoachmanMIS LiasonCommented:
<No points wanted,>
... also note that the double quotes may not be needed around the last zero.

They wont hurt, but they are not needed.

Notice how you did not need them around the zeros in the other two expressions...

bobmillingtonAuthor Commented:
Great solution......many thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now