null values on strings and dates.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CES - 22 Nov 2006 20:20 GMT

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

Reply to this Message

Douglas J. Steele - 22 Nov 2006 20:59 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> All,
> I was hoping someone would be able to help me with null values on strings
[quoted text clipped - 41 lines]
>
> End Function

Reply to this Message

CES - 22 Nov 2006 22:35 GMT

> 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
[quoted text clipped - 3 lines]
> 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

Reply to this Message

Douglas J. Steele - 23 Nov 2006 12:14 GMT

>> 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 <>
[quoted text clipped - 19 lines]
>
> 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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message