nested if

[ddskorupski]

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?

 

[pbaldy]

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

 

[pbaldy]

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

 

[ddskorupski]

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.

 

[pbaldy]

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

 

[ddskorupski]

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"

 

[pbaldy]

Does this work?

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

 

[ddskorupski]

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?

 

[pbaldy]

Did you change the field names?

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

 

[ddskorupski]

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

 

[pbaldy]

Can you post the db?

 

[ddskorupski]

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.

 

[pbaldy]

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

 

[ddskorupski]

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

 

[ddskorupski]

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

 

[pbaldy]

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.

 

[ddskorupski]

almost working

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?

 

[pbaldy]

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.

 

[ddskorupski]

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.

 

[Brianwarnock]

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

Brian