how to correct formula to accommodate null values

[MilaK]

Hello,

The following query formula is throwing an error message when for records that don't have any values in [ExtractionTime].

ActionThree:IIf(([ExtractionTime] Is Not Null) And Nz(([ExtractionTime]>3)),"Consult technologist")


What is the correct syntax?

Thanks,

Mila

 

[Pat Hartman]

ActionThree:IIf(IsNull(ExtractionTime), "Value when null", IIf(ExtractionTime >3,"Consult technologist", "value when < 3"))

You are only returning a value for one of the three possible outcomes so I cleaned up the logic.

 

[Mark_]

I am guessing you want to have a value of "Consult Technologist" ONLY when [ExtrationTime] >3?

If so,
ActionThree: IIf(NZ([ExtractionTime],0) >3,"Consult technologist")

This means you will ALWAYS have a value for [ExtrationTime], and that value will be 0 if the field itself is NULL.

Just a question as it would be relevant for naming conventions, is [ExtrationTime] being used to hold a time component OR is it a count of some sort?

 

[MilaK]

ExtractionTime is a number derived from another query.

I want it to return "consult technologist" when [extraction time] > 3 but it still returning error when [extraction time] is empty.

 

[Mark_]

What "Error" do you want it to return? Or do you want it to be a NULL value?

 

[MilaK]

i don't want it to return error but it's returning error when there is null value.

 

[Mark_]

If you have another query that is creating [extraction time], I would check what values are being used THEIR first. If your current query is being passed a NULL that is because the underlying calculation that creates [extraction time] has an error.

 

[plog]

From what I understand you have 3 cases:

1. ExtractionTime is Null
2. ExtractionTime is >3
3. ExtractionTime is <=3

What do you want ActionThree to be for each of those:

1. ?
2. Consult Technologist
3. ?

Fill in the blanks. Also, there's a very real 4th possibility. It sounds like ExtractionTime is a calculated field built by a query, that means it might throw an Error:

4. ExtractionTime is #Error

What about that case? Also, can you give us the code for how ExtractionTime is built if it is in fact a calculated field?

 

[MilaK]

ExtractionTime: IIf([nucleic_acid_available_date] Is Not Null,DateDiff('d',[blocks_slides_req_date],[nucleic_acid_available_date]),"")

ExractionTime expression returns a number or nothing. There are no errors.

if the number of days is greater than three I want to return "Consult technologist" in another field.

 

[MilaK]

If anyone is interested here is the entire formula that I'm trying to breakdown into parts because I can can't get it to work.

ActionNeeded: IIf(([next_appointment_date] Is Not Null) And ([anticipated_completion_date] Is Not Null) And ([anticipated_completion_date]>[next_appointment_date]),"Consult medical director ",
IIf(([next_appointment_date]<Now()+2) And ([next_appointment_date] Is Not Null) And ([next_appointment_date]>Now()) And ([anticipated_completion_date] Is Null),"Request Anticipated ",
IIf(([ExtractionTime] Is Not Null) And ([ExtractionTime]>3),"Consult technologist ",
IIf(([TimeToReciept] Is Not Null) And ([TimeToReciept]>3),"Call 66800 to",
IIf(([TimeToRequest] Is Not Null) And ([TimeToRequest]>1),"Remind pathologist","")))))

 

[plog]

ExtractionTime can be 3 value types:

A string - specfically "" which is an empty string and can be described as nothing. But it is not equivalent to NULL.

NULL - the lack of a value. When [blocks_slides_req_date] is null, ExtractionTime is Null.

A Number - an integer from the DateDiff function. If you have values in both your dates an integer will be returned.

So that means the 3 cases I outlined are all in play. Since you only told me what you want to occur with 1 of them, this is the expression to use:

ActionThree:IIf([ExtractionTime]>3,"Consult technologist")

 

[plog]

Now that you've shown the full logic. You need to write a custom function to generate the value for ActionNeeded. You've simply tried to cram to much logic into one line of code. Write a function in a module, pass it all the values it will need to make a determination and then return the correct value.

 

[Pat Hartman]

Don't use Now() when you mean Date().

Now() = date + time of day
Date() = date only

 

[MilaK]

@plog

Please give a short example of how to write this sort of function and how to call it in a query that is row source for a form.

Thanks,

Mila

 

[Mark_]

MilaK,

I'd suggest you start using Nz() instead of using Is Not Null. Nz(Field,return) will allow you to forgo using Is Not Null with AND conditions.

As an example, your first condition becomes

IIF( Nz([Anticipated_Completion_Date],0) > Nz([Next_Appointment_Date],0), 
"Consult Medical Director",

instead of

IIf(([next_appointment_date] Is Not Null) 
And ([anticipated_completion_date] Is Not Null) 
And ([anticipated_completion_date]>[next_appointment_date]),
"Consult medical director ",

This will simply reading your code and allow less chance of an error.

 

[MilaK]

@Mark

what's wrong here?

ActionTwo: IIf(Nz([next_appointment_date],0)<Date()+2 And Nz([next_appointment_date],0)<Date(),"Request Anticipated")

 

[Mark_]

@Mark

what's wrong here?

ActionTwo: IIf(Nz([next_appointment_date],0)<Date()+2 And Nz([next_appointment_date],0)<Date(),"Request Anticipated")

IIF(CONDITION,TRUE,FALSE)

You are missing a "FALSE" value.

Also, why are you testing [next_appointment_date] against both Date() AND Date()+2?

 

[MilaK]

Error message says that I'm missing a bracket or a vertical bar.

ActionTwo: IIf(Nz([next_appointment_date],0) < Date() + 2 And (Nz([next_appointment_date],0) > Date(),"Request Anticipated","false")

 

[Mark_]

Error message says that I'm missing a bracket or a vertical bar.

ActionTwo: IIf(Nz([next_appointment_date],0) < Date() + 2 And (Nz([next_appointment_date],0) > Date(),"Request Anticipated","false")

For that error, it should read as

ActionTwo: IIf(Nz([next_appointment_date],0) < Date() + 2 And (Nz([next_appointment_date],0) > Date()[B][COLOR="SeaGreen"])[/COLOR][/B],"Request Anticipated","false")

Note: You don't have to return a text literal of "false", IIF just requires a return value for FALSE results. It could be "".

 

[isladogs]

Error message says that I'm missing a bracket or a vertical bar.

ActionTwo: IIf(Nz([next_appointment_date],0) < Date() + 2 And (Nz([next_appointment_date],0) > Date(),"Request Anticipated","false")

Looks to me that you could simplify this as

Iif(Nz([next_appointment_date],0)=Date+1,"Request Anticipated", "false")