Null string in VBA (1 Viewer)

[hycho]

Hi,


Does anyone know how I can read in a blank (or empty) cell and spit out an empty value? See my code below, as I have everything set up except for the null string.



Option Compare Database

Function cnty(x) As String
Select Case x
Case "Bronx", 5: cnty = "Bronx"
Case "New York", 61: cnty = "New York"
Case "Kings", 47: cnty = "Kings"
Case "Queens", 81: cnty = "Queens"
Case "Richmond", 85: cnty = "Richmond"
Case "Suffolk", 103: cnty = "Suffolk"
Case "Nassau", 59: cnty = "Nassau"
Case "Westchester", 119: cnty = "Westchester"
Case cnty = vbNullString
Case Else: cnty = "Upstate NY"
End Select
End Function


Thanks,
Henry

 

[pr2-eugin]

Have you tried

[B]
Case "" [/B]: Cnty=vbNullString

 

[mdlueck]

I would handle the null condition first before entering the case statement. That way you would be guaranteed a non-null value in the variable. Perhaps use a simple if statement for the null check.

 

[hycho]

Have you tried

[B]
Case "" [/B]: Cnty=vbNullString

I have tried "" and " ", and both methods doesn't work. Thanks.

 

[hycho]

I would handle the null condition first before entering the case statement. That way you would be guaranteed a non-null value in the variable. Perhaps use a simple if statement for the null check.

How do I identify null values before entering the case statement? Thanks.

 

[pr2-eugin]

Or as suggested by Michael, try to check for EMPTY STRING before select.. Try adding this.

Function cnty(x) As String
If Len(x & "")=0 Then
    cnty = vbNullString
    Exit Function
Else
    'your select statement
End If
End Function

You can alternatively use Nz(), but the above will work too.. Give it a go..

 

[mdlueck]

Thanks for helping out!

Function cnty(x) As String
If Len(x & "") = 0 Then
    cnty = vbNullString
    Exit Function
End If
'your select statement
End Function

I was thinking like that, since the If is going to Exit, no need to wrap the Case within the Else.

 

[vbaInet]

Or even:

Select Case Nz(x, vbNullString)
    Case vbNullString: cnty = vbNullString

... if you don't have any special action to take when it's Null.

 

[hycho]

Or as suggested by Michael, try to check for EMPTY STRING before select.. Try adding this.

Function cnty(x) As String
If Len(x & "")=0 Then
    cnty = vbNullString
    Exit Function
Else
    'your select statement
End If
End Function

You can alternatively use Nz(), but the above will work too.. Give it a go..

This works well. Thanks.