Start a new topic

Custom Function - Adding a value that might be Null

A big thank you to all who have contributed so to this forum. I've found quite a bit of useful information.

I'm attempting to add a virtual field that will use a custom function. That function will add the value of Y, Z and AA together and multiple that total by another calculated number. The formula I have works if all the values for Y, Z and AA are filled in, but if one of them is null I get the error "Input string was not in a correct format."

I tried using the Nz( ) function, but either I'm using it incorrectly or it's not being recognized. Any help would be appreciated. Here is the cod


Public Function ComputedColumn_635373154936096151( _

ByVal oField as ImportOM.API.iField, _

ByVal Cancel As ImportOM.API.iCancel) As Integer

Dim sReturn As Integer = 0, Hours As Integer = DateDiff("h",import.fields.GetByExcelName("P").Value, import.fields.GetByExcelName("Q").Value), UnregAdults As Integer = import.fields.GetByExcelName("Y").Value, UnregMinor As Integer = import.fields.GetByExcelName("Z").Value, ClockedIn As Integer = import.fields.GetByExcelName("AA").Value

If import.fields.GetByExcelName("X").Value = "Cancelled" Then

sReturn = 0

Else sReturn = ((UnregAdults+UnregMinor+ClockedIn)*Hours)

End If

Return sReturn

End Function

All of the fields come in as strings. VB tries to convert them for you but it doesn't know what to do with a blank string.

Try splitting the declaration of the variable and the assignment and check for the blank there.

Dim UnregAdults As Integer
If import.fields.GetByExcelName("Y").Value = "" Then
UnrefAdults = 0
UnrefAdults = import.fields.GetByExcelName("Y").Value
End If

That worked! Thank you very much for the help.

Login or Signup to post a comment