help iif condition in report

[eddii]

i have report which opens by aform

there is field in a query named duecheck if its value is 1
then in report it should show statucer value
or else pending

this is the formula

=IIf([duecheck]=1,[STATUSCER],"pending")

it shows this error if the value is true #Error
instead of showning statuscer value

if false it shows pending

steps
open forms = reportform
select year =2011
select shop = qd
select month = jan
click " calibraiton month status "

the error is shown in report

pls help database atatched

 

[vbaInet]

eddii,

The first thing to note that if duecheck is a Yes/No field, Yes is -1 and No is 0. So when the value is 1 it should display "pending" because 1 meets the second criteria of the IIF() function.

In any case, your problem might be that duecheck is Null. So in the meantime, try this:

=IIf(Nz([duecheck], 0) = -1, [STATUSCER], "pending")

 

[eddii]

thanks for help vbaInet : help iif condition in report

thanks for help vbaInet
but this is not working as some data for statsucer is "ok"

duechek field in query code

duecheck: IIf([NEXTCALDUEDATE]=[SYSNEXTDUEDATE],1,0)

it shows pending

but this #error is not desplayed but shows pending

could you pls check in the database if you are free

 

[vbaInet]

What is the data type of duecheck?

 

[eddii]

duecheck is a expression inserted
i have not given any data type it may be default data type

 

[vbaInet]

So what is the Expression?

 

[eddii]

duechek expression field in query code

format is general number

duecheck: IIf([NEXTCALDUEDATE]=[SYSNEXTDUEDATE],1,0)

 

[vbaInet]

So your original code was fine.

Does [STATUSCER] or [NEXTCALDUEDATE] or [SYSNEXTDUEDATE] ever contain Null? What kind of values do you have in [STATUSCER]?

 

[eddii]

Yes [statuscer] & [NEXTCALDUEDATE] contains null value

data of [statuscer] = text format
[statuscer]
ok
ng
scrap
pending
missing


data of = [NEXTCALDUEDATE] = date/time format

data of = [SYSNEXTDUEDATE] = date/time format

 

[vbaInet]

Ok, I decided to look at your db because I had a bit of time

The name of the control is [STATUSCER] which is the same as the field and it is causing a Circular Reference. Rename the textbox to txtSTATUSCER and when you do that make sure the field name is still [STATUSCER].

 

[eddii]

thanks for the reply boss
i tried but i think i was wrong

if possible pls say the procedure to do or change in db

if no time no issues i will try in the way you told once again

 

[vbaInet]

I've attached it.

 

[eddii]

thanks the report is working
the change is done in report name txtstatuscer

thankyou very much
and aslo thanks for responding my other query in table

 

[vbaInet]

You're welcome!