IIF Statement Returning #Error

[DrDefpoints]

I haven't use Access for a very long time so I may just be missing something simple. The problem is as follows:



I'm using the following IFF statement in a Query:

Expr1: IIf([ODG-Bill]![Date-PO-Rcvd]="",[ODG-Bill]![Fee1],"")

So what I'm going for is to have it return the fee amount listed in the Fee field if the PO Rcvd Date is Blank.

What I'm getting is a blank for False (This i want I want) and "#Error" for True.

What I'm I missing

Thanks
Jeff

[ajetrumpet]

is this in a form field?? form fields are denoted by the field names enclosed in []. Nothing else required.

what is ODG-Bill??

[DrDefpoints]

[ODG-Bill] is the Table name and [Date-PO-Rcvd] is the field name it that table. I'm entering the Expertion into the Field box of a new Query. I used the expression builder to build this and it inserted the [ODG-Bill]![Date-PO-Rcvd] when I selected it.

Removing the [ODG-Bill]! from the equation did not help. I still get the #error in the Query for all True records

[ajetrumpet]

i've seen this done a million times. some claim it works, others claim not.

use DLOOKUP instead, in your control source. that doesn't provide #ERROR, ever.

I cant remember the return value if it doesnt find an entry, but I dont think it's #error.

[orange]

[ODG-Bill] is the Table name and [Date-PO-Rcvd] is the field name it that table. I'm entering the Expertion into the Field box of a new Query. I used the expression builder to build this and it inserted the [ODG-Bill]![Date-PO-Rcvd] when I selected it.

Removing the [ODG-Bill]! from the equation did not help. I still get the #error in the Query for all True records

If Date-PO-Rcvd is a Date data type, I don't think a missing entry will be a zero length string (""). I think you should be checking for
IsNull([Date-PO-Rcvd]). And, if Fees is a numeric value, then false would return 0 ( in my view)

MyFees: IIf(IsNull([ODG-Bill]![Date-PO-Rcvd]),[ODG-Bill]![Fee1],0)

But, more important is the meaning of the iif statement.

To me you are saying:
If there is no [Date-PO-Rcvd], then use [ODG-Bill]![Fee1]
which seems a little confusing. Perhaps you could explain what you are trying to accomplish. It could be you have just used the default Expr1 that Access assigns, and not something like MyFees: or similar.

As Adam pointed out

What is ODG-Bill?

there are several naming approaches that identify Tables etc, and don't use special characters "-".
Following a naming convention can save a lot of headaches and reduce confusion if someone, other than you, has to read/modify the code.

[DrDefpoints]

If Date-PO-Rcvd is a Date data type, I don't think a missing entry will be a zero length string (""). I think you should be checking for
IsNull([Date-PO-Rcvd]). And, if Fees is a numeric value, then false would return 0 ( in my view)

MyFees: IIf(IsNull([ODG-Bill]![Date-PO-Rcvd]),[ODG-Bill]![Fee1],0)

This worked perfect Thanks... I'll look into naming conventions

To me you are saying:
If there is no [Date-PO-Rcvd], then use [ODG-Bill]![Fee1]

Yes you are exactly right. I am trying to generate a report that has two columns one column is The fees we have received a PO (Purchase Order) for and one is for Fees we have not Received a PO for. When we receive a PO a date is entered, hence I figured an Expression to check whether there was a date or not would accomplish my task. If there is a better way I'm all ears. Next I'd need to check VS two different fields and was just going to use a Nested IIF statement.

Thanks Again

[orange]

Glad to help.
Good luck with the naming, it is important.

Here's a link you might find interesting - even the first few topics.
http://www.rogersaccesslibrary.com/forum/topic238.html