null values on strings and dates.

[CES]

All,
I was hoping someone would be able to help me with null values on strings and dates being passed into a Module.

I know that I can test on a field for a null value using IIf([Field]<>"",getFullName([Field]),"") however I'm having difficulty figuring out how to match the IIF() in a module.

When I pass in a null field into the module via tmp and it's an empty value in a string data type, it returns #Error and when it's a null value in a date field, it returns 12:00:00 AM.

Any help on this would be greatly appreciated. Thanks in advance. - CES



Public Function getFullName(tmp As String) As String

Dim strToReturn As String
strToReturn = ""

Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT [cID], [cSir], [cFName], [cMName], [cLName] FROM Contacts WHERE ((([cID])=" & tmp & "));"

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Open Source:=strSQL, CursorType:=adOpenKeyset, Options:=adCmdText
If Not .EOF Then

If .Fields("cSir") <> "" Then
strToReturn = .Fields("cSir"))
End If


End If

End With

rst.Close
Set rst = Nothing

getFullName = strToReturn

End Function

 

[Douglas J. Steele]

You don't check for Nulls using <> "". Null is not the same as a zero-length
string. And, in fact, you don't check for Null using = or <> at all: you use
the IsNull function.

If Not IsNull(.Fields("cSir")) Then
strToReturn = .Fields("cSir"))
End If

 

[CES]

You don't check for Nulls using <> "". Null is not the same as a zero-length
string. And, in fact, you don't check for Null using = or <> at all: you use
the IsNull function.

If Not IsNull(.Fields("cSir")) Then
strToReturn = .Fields("cSir"))
End If

Doug,
It's not the .Fields("xxx")) that I'm having a problem with, it's the var tmp being past into the function.

Public Function getFullName(tmp As String) As String

If IsNull(tmp) Then
tmp="0"
End If

If Not IsNull(tmp) Then
tmp="0"
End If

Unfortunately either of the two snippets about, don't work. -- CES

 

[Douglas J. Steele]

Doug,
It's not the .Fields("xxx")) that I'm having a problem with, it's the var
tmp being past into the function.

Public Function getFullName(tmp As String) As String

If IsNull(tmp) Then
tmp="0"
End If

If Not IsNull(tmp) Then
tmp="0"
End If

Unfortunately either of the two snippets about, don't work. -- CES

String variables cannot be Null. The only data type that can accept a Null
value is a Variant.