how to correct formula to accommodate null values (1 Viewer)

plog

Banishment Pending
Local time
Today, 17:05
Joined
May 11, 2011
Messages
11,612
Please everyone read post #10 and stop trying to fix this portion of the issue:

https://www.access-programmers.co.uk/forums/showpost.php?p=1558788&postcount=10

The total logic is just too complex to fit into one expression, you need a custom function. When you do that, in a query it will look like this:

ActionNeeded: get_ActionNeeded([next_appointment_date], [anticipated_completion_date], ...{all values needed to determine Action Needed}...)

Then in a module, you will have code that implements your logic like so:

Code:
Public Function get_ActionNeeded(NextAppointment, AnticipatedCompletion, ....{all values needed to determine Action Needed}...)
  ' takes set of data and determines the action neeeded

  ret = "Error"  
  ' return value, error by default

  If (NextAppointment Is Not Null) And (AnticipatedCompletion Is Not Null) Then ret ="Consult medical director "
  If (NextAppointment < (Date() + 2) AND (NextAppointment Is Not Null) Then ret ="Request Anticipated ",
  ...
  ...
  more logic expressions
  ...
  ...


  get_ActionNeeded = ret
  ' returns correct value

  End Function
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:05
Joined
May 7, 2009
Messages
19,169
ActionThree: IIf((Switch([nucleic_acid_available_date] Is Null,0,True,DateDiff('d',[blocks_slides_req_date],[nucleic_acid_available_date]))) > 3,"Consult technologist", "")
 

MilaK

Registered User.
Local time
Today, 15:05
Joined
Feb 9, 2015
Messages
285
@plog

I get run time error 424. object required. and I can't get the error to go away.

Option Compare Database

Public Function get_ActionNeeded(NextAppointmentDate, AnticipatedCompletionDate, ExtractionTime, TimeToReciept, TimeToRequest)
' takes set of data and determines the action neeeded

ret = "Error"
' return value, error by default

If (NextAppointmentDate Is Not Null And AnticipatedCompletionDate Is Not Null And AnticipatedCompletionDate > NextAppointmentDate) Then
ret = "Consult medical director"
End If

If (NextAppointmentDate < (Date + 2) And (NextAppointmentDate Is Not Null) And (AnticipatedCompletionDate Is Null)) Then
ret = "Request Anticipated"
End If

If (ExtractionTime > 3 And (ExtractionTime Is Not Null)) Then
ret = "Consult technologist"
End If

If ((TimeToReciept > 3) And (TimeToReciept Is Not Null)) Then
ret = "Call 66800"
End If

If ((TimeToRequest > 1) And (TimeToRequest Is Not Null)) Then
ret = "Remind pathologist"
End If


get_ActionNeeded = ret
' returns correct value

End Function

I'm calling the function form a query that is a row source for a form.

Code:
ActionNeeded: get_ActionNeeded([next_appointment_date], [anticipated_completion_date], [ExtractionTime], [TimeToReciept], [TimeToRequest])

What am I doing wrong? thanks
 

plog

Banishment Pending
Local time
Today, 17:05
Joined
May 11, 2011
Messages
11,612
Does any line get highlighted when the error is thrown? What is the value passed to the function causing it?

If the compiler won't tell you, divide and conquer until you can deduce it yourself. Comment all but 1 set of logic out and rerun to see if it works. If it does, then keep adding the logic back in until it breaks again. That one is the offender
 

MilaK

Registered User.
Local time
Today, 15:05
Joined
Feb 9, 2015
Messages
285
@plog
As it turned out, VBA editor doesn't like Is Not Null and prefers <> "".


Code:
Public Function get_ActionNeeded(NextAppointmentDate, AnticipatedCompletionDate, ExtractionTime, TimeToReciept, TimeToRequest)
  ' takes set of data and determines the action neeeded
  Debug.Print "NextAppointmentDate " & NextAppointmentDate
  Debug.Print "AnticipatedCompletionDate " & AnticipatedCompletionDate
  Debug.Print "ExtractionTime " & ExtractionTime
  Debug.Print "TimeToReciept " & TimeToReciept
  Debug.Print "TimeToRequest " & TimeToRequest
  
  ret = "Error"
  ' return value, error by default

  If (NextAppointmentDate <> "" And AnticipatedCompletionDate <> "" And AnticipatedCompletionDate > NextAppointmentDate) Then
  ret = "Consult medical director"
  End If
  
  If (NextAppointmentDate < (Date + 2) And (NextAppointmentDate <> "") And (AnticipatedCompletionDate = "")) Then
  ret = "Request Anticipated"
  End If
  
  If (ExtractionTime > 3 And (ExtractionTime <> "")) Then
  ret = "Consult technologist"
  End If
  
  If ((TimeToReciept > 3) And (TimeToReciept <> "")) Then
  ret = "Call 66800"
  End If
  
  If ((TimeToRequest > 1) And (TimeToRequest <> "")) Then
  ret = "Remind pathologist"
  End If
  
  
  get_ActionNeeded = ret
  ' returns correct value

  End Function
 

Mark_

Longboard on the internet
Local time
Today, 15:05
Joined
Sep 12, 2017
Messages
2,111
If you do not tell ACCESS what type each variable is, ACCESS makes a guess. I'd recommend telling ACCESS what you do expect. If your function is supposed to be able to accept a NULL, make sure you type each variable as VARIANT.

Code:
Public Function get_ActionNeeded(NextAppointmentDate, AnticipatedCompletionDate, ExtractionTime, TimeToReciept, TimeToRequest)

would become

Code:
Public Function get_ActionNeeded(NextAppointmentDate as VARIANT, AnticipatedCompletionDate as VARIANT, ExtractionTime as VARIANT, TimeToReciept as VARIANT, TimeToRequest)

This would allow you to test for NULL values being passed.
 

Users who are viewing this thread

Top Bottom