Whats wrong with this: =IIf([OrdPmtAmt]>0,"Paid","UnPaid") ??

[Mel]

I've got the above in the Control Source property field on a Report.

The report calls a multi-talble query for the data...

One of the tables has the field OrdPmtAmt

that field contains the amount paid on an order... else either null or
zero.

When I run the report I get an Error.

What am I doing wrong?

Thanks for any help.,

 

[Clif McIrvin]

A Null value always causes an expression to return Null, so your
expression will fail when it hits a Null. Use the nz function to return
a zero instead of a Null:

=IIf(nz([OrdPmtAmt],0)>0,"Paid","UnPaid")

 

[Mel_3]

Still no workie.
I copied your expression and put in the Report Field
=IIf(nz([OrdPmtAmt],0)>0,"Paid","UnPaid")

I checked the field in the underlying table and it is "General Number"
with 2 decimal places.

So I have the Report calling the query which calls about 4 tables...

in tblOrders I have a field OrdPmtAmt where we enter the amount
actually paid.

Instead of showing the amount paid I want to show either Paid or
UnPaid in the Report so I place this expression in the Report field...
=IIf(nz([OrdPmtAmt],0)>0,"Paid","UnPaid")

Do you think we need to tell the Report which of the underlying tables
we are referenceing?

If so how would you show that in the expression?

Thanks again for the help folks.

 

[Clif McIrvin]

Do you think we need to tell the Report which of the underlying tables

we are referenceing?

That was another possibility I wodered about.

Add OrdPmtAmt to the query that the report references, and your
expression should work.

The report doesn't know anything at all about the underlying tables --
it is only looking at the query that is its recordsource. If OrdPmtAmt
isn't defined as one of the columns in your recordsource query, the
report cannot find it; on the other hand when you reference a fieldname
(ie, [OrdPmtAmt] ) in a report expression the report looks for that
fieldname as one of the columns of your recordsource.


If you still get an error, please post back with the exact text of the
error message.

--
Clif

Still no workie.
I copied your expression and put in the Report Field
=IIf(nz([OrdPmtAmt],0)>0,"Paid","UnPaid")

I checked the field in the underlying table and it is "General Number"
with 2 decimal places.

So I have the Report calling the query which calls about 4 tables...

in tblOrders I have a field OrdPmtAmt where we enter the amount
actually paid.

Instead of showing the amount paid I want to show either Paid or
UnPaid in the Report so I place this expression in the Report field...
=IIf(nz([OrdPmtAmt],0)>0,"Paid","UnPaid")

Do you think we need to tell the Report which of the underlying tables
we are referenceing?

If so how would you show that in the expression?

Thanks again for the help folks.

 

[Clif McIrvin]

I checked the field in the underlying table and it is "General Number"
with 2 decimal places.

You just told us the format properties for the field, not it's data
type. What is significant about the data itself in this case is not how
it's formatted, but 1) that it is in fact a number data type so that
testing for greater than zero actually makes sense, and 2) the fact that
you mentioned that Null values are possible. As I already mentioned (and
you implemented) Nulls *do not* behave like zeros -- so when Null values
are possible they must be accounted for.

 

[Mel_3]

Still no workie... the column in Report shows #Error

BUT... =IIf([OrdPmtAmt] Is Null,"Paid","UnPaid") Does work ! (And
properly reports that none of the OrdPmtAmt fields are Null)

'Is not Null' also works...

But =IIf([OrdPmtAmt]=0,"Paid","UnPaid") Does NOT work... and the
OrdPmtAmt does equal zero in some records...

and, for what it's worth... If I replace the Expression with the field
name OrdPmtAmt... all the values show up...

I'm stumped... any more idea's?

Thanks for the help.

 

[Clif McIrvin]

You have shared the Format and Decimal Places properties for
OrdPmtAmt --- what are the DataType and Field Size properties from the
table definition? I'm thinking that [OrdPmtAmt]=0 is not behaving as
expected because of a data type issue.

Another simple test is to build a simple query against the table which
contains OrdPmtAmt and put your expression in a caluclated column and
see what that query shows in datasheet view:

SELECT [tblOrders].[OrdPmtAmt], iff([OrdPmtAmt]=0,"Zero","Not Zero") AS
Expr1
FROM [tblOrders];

--
Clif

Still no workie... the column in Report shows #Error

BUT... =IIf([OrdPmtAmt] Is Null,"Paid","UnPaid") Does work ! (And
properly reports that none of the OrdPmtAmt fields are Null)

'Is not Null' also works...

But =IIf([OrdPmtAmt]=0,"Paid","UnPaid") Does NOT work... and the
OrdPmtAmt does equal zero in some records...

and, for what it's worth... If I replace the Expression with the field
name OrdPmtAmt... all the values show up...

I'm stumped... any more idea's?

Thanks for the help.

 

[Mel_3]

Cliff,

I think I have it. I had used the field in the Report to show the
actual OrdPmtAmt before... then changed it to the expression...

But... I guess I didn't "unbind" it from the OrdPmtAmt field... thus
the error...

Once I had an Unbound field on the Report... and put the expression it
it... it worked.

Thank you for all your help. I'm on deadline and have a long night
ahead !

Thanks again!

 

[KARL DEWEY]

=IIf([OrdPmtAmt] Is Null,"Paid","UnPaid")
=IIf([OrdPmtAmt]=0,"Paid","UnPaid")

If [OrdPmtAmt] contains no value (null) or a value of 0 (zero) why would you
say it is 'Paid'? That does not make sense.

--
Build a little, test a little.

You have shared the Format and Decimal Places properties for
OrdPmtAmt --- what are the DataType and Field Size properties from the
table definition? I'm thinking that [OrdPmtAmt]=0 is not behaving as
expected because of a data type issue.

Another simple test is to build a simple query against the table which
contains OrdPmtAmt and put your expression in a caluclated column and
see what that query shows in datasheet view:

SELECT [tblOrders].[OrdPmtAmt], iff([OrdPmtAmt]=0,"Zero","Not Zero") AS
Expr1
FROM [tblOrders];

--
Clif

Still no workie... the column in Report shows #Error

BUT... =IIf([OrdPmtAmt] Is Null,"Paid","UnPaid") Does work ! (And
properly reports that none of the OrdPmtAmt fields are Null)

'Is not Null' also works...

But =IIf([OrdPmtAmt]=0,"Paid","UnPaid") Does NOT work... and the
OrdPmtAmt does equal zero in some records...

and, for what it's worth... If I replace the Expression with the field
name OrdPmtAmt... all the values show up...

I'm stumped... any more idea's?

Thanks for the help.

 

[Clif McIrvin]

Great! Thanks for the update --- glad it's working..

 

[Clif McIrvin]

=IIf([OrdPmtAmt] Is Null,"Paid","UnPaid")
=IIf([OrdPmtAmt]=0,"Paid","UnPaid")

If [OrdPmtAmt] contains no value (null) or a value of 0 (zero) why
would you
say it is 'Paid'? That does not make sense.

Karl, the original expression was ...>0...; in the course of trying to
get something working the constants were not changed -- so, the original
(desired) expression did make sense.

Sounds like the OP got it sorted.

--
Clif

--
Build a little, test a little.

You have shared the Format and Decimal Places properties for
OrdPmtAmt --- what are the DataType and Field Size properties from
the
table definition? I'm thinking that [OrdPmtAmt]=0 is not behaving as
expected because of a data type issue.

Another simple test is to build a simple query against the table
which
contains OrdPmtAmt and put your expression in a caluclated column and
see what that query shows in datasheet view:

SELECT [tblOrders].[OrdPmtAmt], iff([OrdPmtAmt]=0,"Zero","Not Zero")
AS
Expr1
FROM [tblOrders];

--
Clif

Still no workie... the column in Report shows #Error

BUT... =IIf([OrdPmtAmt] Is Null,"Paid","UnPaid") Does work ! (And
properly reports that none of the OrdPmtAmt fields are Null)

'Is not Null' also works...

But =IIf([OrdPmtAmt]=0,"Paid","UnPaid") Does NOT work... and the
OrdPmtAmt does equal zero in some records...

and, for what it's worth... If I replace the Expression with the
field
name OrdPmtAmt... all the values show up...

I'm stumped... any more idea's?

Thanks for the help.