nested if

[ddskorupski]

Brian, thanks for the response. By changing to 1 my iff statement stopped working all together. right now it is working it is just adding #error to the yes piece

 

[Brianwarnock]

In my copy of your Db changing to 1 gave an N as one would expect as the date wasnot null.

Brian

No that's not right is it?

 

[ddskorupski]

it gave an n to all records even when there was a date

 

[pbaldy]

The DocTypeID field is not in the query. If I add it, I notice that the only records that have a value there are the ones with a date in the other field. The others are Null. This gets rid of the error:

Expr3: IIf(nz([DocTypeID],"")="1" And IsNull([documentreceived]),"N","Y")

but all records are "Y", because the 2 conditions are never met.

 

[ddskorupski]

I am sure this has something to do with the fact that it is a date field with a valid date in it that produces the #error. Am I asking the expression to change a date value to a string value?

 

[ddskorupski]

interesting. my query works. It returns a N value when there is no date in the documentreceived field but a #error when there is a date

 

[Brianwarnock]

I've got to go but am puzzled as doctypeid is defined in the table as long integer but is left justified.

Brian

 

[Brianwarnock]

IIf(nz([DocTypeID],"")="1" And IsNull([documentreceived]),"N","Y")

works because Nz returns a string therefore "1" is correct,but

IIf(DocTypeID]=1 And IsNull([documentreceived]),"N","Y")

Also works because DocTypeID is defined as a number. i,e, long integer

I removed a date from documentreceived and got a N, all other results are "Y" so everything is as expected.
Brian

 

[ddskorupski]

I get "invalid syntax" whenI try to use your examples

 

[ddskorupski]

when using: IIf(nz([DocTypeID],"")="1" And IsNull([documentreceived]),"N","Y")

I get a Y for every record. even those that have no date

 

[Brianwarnock]

As Paul pointed out in the database we have all records without a date also have a null DatatypeID, so the 2 conditions are never met.

Brian

PS perhaps you are doing the wrong test, can you say in English what you require instead of code?

 

[ddskorupski]

I don't understand what you are saying. The database I sent has dates in the documentsreceived field. When I run the query it does not return all dates. I can see which fields have dates and which do not. The docid is not null it just is not being displayed in the query.

When I run this query I see the field documentreceived with the records that have dates and records that do not. Exp3 shows me an N for those records without the dates, therefore that piece is correct. But it shows me #Error for those records with a date.

I just do not understand what you are saying. The query seems to be working with the exception of not returning a Y but instead returning a #error

 

[Brianwarnock]

See attached

Brian

 

[ddskorupski]

I want to once again thank all of you who helped with this if statement. I did get it to work. Here is my iif statement:

Expr6: IIf(IsNull([documentreceived]),"No",IIf([doctypeid]=2,"yes","no"))

 

[Brianwarnock]

Thank you for the feedback, I'm glad you have resolved your problem, which was as I started to suspect a different one to that which we were originally addressing our efforts.

best wishes

brian