thanks again to all who helped earlier. The if statment (Expr3: IIf(IsNull([document received]),"No","Yes") worked great!

Now I need a nested if statement. It needs to read if doctype equals result and if document received isnotnull, then yes else no

how would that look?

Generally, you replace "No" and/or "Yes" with another complete IIf() function.

BTW, you might look at the Switch() function, so see if it fits your needs better.

what is a switch function and how would that look?

I want the function to look at doctype and see if it matches result. if it does I then want it to look at doc received date to see if it has a date; if it has both result and date I want it to return yes.

How would a switch do that?

Thanks for the help by the way.

Switch() is well described in VBA help, though in this case the IIf() is probably just as good. It also sounds like a single IIf() with 2 tests may serve your needs, though I'm not sure I fully understand the goal.

IIf(DocType = Result AND IsDate(ReceivedDate), "Yes", "No")

I have a field called doctype. this field has three values: script, result, approval. They are all considered documents.

When a script comes in it is marked as script and given a document received date.

When a document comes in that is a result it is tagged as result and given a document received date.

So I want to look at the document received date to see if it is null then look at the doctype field to see if it says result. If it says result and there is a date I want to return "yes"

Does this work?

IIf(doctype = "Result" AND IsDate(ReceivedDate), "Yes", "No")

no. the date field is called document received and it either has a date or is empty. What I want to show is this:

field: doctypeid - if doctypeid = result and if
field: document received is not null, then "y"
else
(show nothing)

does that help?

Did you change the field names?

IIf(doctypeid = "Result" AND IsDate([document received]), "Yes", "No")

yes, I changed the field names. It returns an error

Can you post the db?

I cannot because the info is confidential. Let me try it this way:

document table has two fields:

doctypeid - will either be script, result, or approval
document received - date field showing one of the three above is received

query is looking for:

if the doctypeid is equal to result and the document received date has a date in it, then return back a yes (meaning the document was received) or no (the document was not received).

I wish I could send the database,.....would make it easier. Thanks for your help.

I believe the formula I gave will do that. Is doctypeid a number rather than the actual text? IOW, should we be testing for 2 instead of "result"?

I tried changing it to 2. I think what it doesn't recognize is the isdate date received.

doesn't the isdate convert a function to a date? My documents received field is already a date field. I just want to check if it is null or not

From Help:

IsDate returns True if the expression is a date or is recognizable as a valid date; otherwise, it returns False

I use it to make sure the user hasn't entered something goofy. If you simply want to test whether the field is Null, you can change that to IsNull(). If that works, that would imply that field is not really a date field.

okay. I almost have it working. Here is my nested iif:

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

"1" represents script

For all dates that are null my expression is returning N. for all that has dates it is returning #error.

Any ideas?

I can't think of why it would error if the test isn't met. We certainly don't want anything confidential posted, but can you put the table and query in a new db, get rid of anything confidential in the table, and post that? This would be a lot easier if I could see what's going on.

thanks very much Paul for all of your help. Attached is a db with some info. The query is where you will see that it is returning the correct value if the date field is null but not if there is a date in it. I am wondering if it has to do with strings or something.

DoctypeID is a number, tho' how you got it left justified is a mystery to me, anyway remove the quotes. just =1

Brian

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

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?

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

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.

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?

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

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

Brian

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

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

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

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

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?

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

See attached

Brian

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"))

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