Custom Function - Adding a value that might be Null
C
Casey Ferrell
started a topic
almost 8 years ago
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 code:
_
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
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 Else UnrefAdults = import.fields.GetByExcelName("Y").Value End If
Casey Ferrell
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 code:
_
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