how to correct formula to accommodate null values

[MilaK]

@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.

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

What am I doing wrong? thanks

 

[Minty]

You need to declare what each parameters datatype is.

http://www.fontstuff.com/vba/vbatut04.htm

 

[plog]

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]

@plog
As it turned out, VBA editor doesn't like Is Not Null and prefers <> "".

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_]

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.

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

would become

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.