Custom function; Handling a field with no data

[SigAdams]

I have a custom function that determines in a value is between two numbers.

My problem is when a field is blank it throws an error.

How can I handle this?

'Call Weight
Public Function Call_Weight(ByVal scoreVal As Double)

    If scoreVal >= 0.85 Then x = 10
    If scoreVal <= 0.849 And scoreVal >= 0.8 Then x = 9
    If scoreVal <= 0.799 And scoreVal >= 0.75 Then x = 8
    If scoreVal <= 0.749 And scoreVal >= 0.7 Then x = 7
    If scoreVal <= 0.699 And scoreVal >= 0.65 Then x = 6
    If scoreVal <= 0.649 And scoreVal >= 0.6 Then x = 5
    If scoreVal <= 0.599 And scoreVal >= 0.55 Then x = 4
    If scoreVal <= 0.549 And scoreVal >= 0.5 Then x = 3
    If scoreVal <= 0.499 And scoreVal >= 0.45 Then x = 2
    If scoreVal <= 0.449 And scoreVal >= 0.4 Then x = 1
    If scoreVal <= 0.399 Then x = 0
    
    Call_Weight = x

End Function

 

[dhookom]

Your X is undefined and you haven't suggested what value should be returned if scoreVal is null.

I typically don't like to hard code values. You should have a table of min and max values with the Call_Weight.

Try:

Public Function Call_Weight(ByVal scoreVal As Variant)
  Dim X As Integer
  If Not IsNull(scoreVal) Then
    Select Case scoreVal
      Case Is <= 0.399
        X = 0
      Case Is <= 0.449
        X = 1
      Case Is <= 0.499
        X = 2
      Case Is <= 0.549
        X = 3
      Case Is <= 0.599
        X = 4
      Case Is <= 0.649
        X = 5
      Case Is <= 0.699
        X = 6
      Case Is <= 0.749
        X = 7
      Case Is <= 0.799
        X = 8
      Case Is <= 0.849
        X = 9
      Case Else
        X = 10
    End Select
   Else  'Don't know what goes here
    X = 0
  End If
      
  Call_Weight = X

End Function

Duane
Hook'D on Access
MS Access MVP

 

[smedvid]

In addition to Null... I've had issues where users entered a space into a field... at least in my case I checked if the value was Is Number and also if [field]=''. htwh

Steve Medvid
IT Consultant & Web Master

 

[Andrzejek]

SigAdams,
Your [tt]Function Call_Weight[/tt] probably works OK, the problem may be the way you call it. If you have something like:

[tt]SomeValue = Call_Weight(Me!txtMyTextBox)[/tt]

and in txtMyTextBox user can type anything they want, including space(s), that will not work - because your Function will accept a Double. And a space or an empty string is not a Double.

In this case you may try:
[tt]SomeValue = Call_Weight([blue]Val([/blue]Me!txtMyTextBox[blue])[/blue])[/tt]

smedvid,
IsNumeric is not a 'full-proof' method.

Try this:

[tt]MsgBox IsNumeric("2e12")[/tt]
or
[tt]MsgBox IsNumeric("2d12")[/tt]

they both return True

Have fun.

---- Andy

 

[smedvid]

Good catch on ISNumeric... I've coded my own in SQL Server previously...

Steve Medvid
IT Consultant & Web Master