"runtime error 94' - invalid use of null" 1

[serino]

I am receiving a runtime error 94 - invalid use of null. This happened when I added this field:
textAlign(rs.Fields("Remarks"), 5)

If I remove this, the code runs fine.

Why is this field producing the error.


[blue]
Public Function textAlign(strtext As String, intColumnWidth As Integer) As String
Dim intSpaces As Integer
Dim counter As Integer
If Len(strtext) >= intColumnWidth Then
textAlign = left(strtext, intColumnWidth)
Else
intSpaces = intColumnWidth - Len(strtext)
For counter = 1 To intSpaces
strtext = strtext & " "
Next counter
textAlign = strtext
End If
End Function

Private Sub Command17_Click()
Dim strEmail, strBody, strtext As String
Dim ObjOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim rs As DAO.Recordset

'**creates an instance of Outlook
Set ObjOutlook = CreateObject("Outlook.application")
Set objEmail = ObjOutlook.CreateItem(olMailItem)
Set rs = Me.RecordsetClone

'**************************************************************
'*create string with email address

strEmail = txtemail

strBody = strBody & "Today's Date: " & txtdate & Chr(13) & Chr(13)
strBody = strBody & "Department:" & " " & Department & Chr(13) & Chr(13) & Chr(13)
strBody = strBody & "Emp#:" & " " & "Last Name" & " " & "First Name" & " " & "Present" & " " & " Remarks" & Chr(13)
strBody = strBody & "________________________________________________________________________________________________________" & Chr(13)

If rs.EOF = False Then
rs.MoveFirst
Do Until rs.EOF [/blue]
[red] strBody = strBody & textAlign(rs.Fields("emp#"), 0) & " " & textAlign(rs.Fields("LSTNME"), 15) & " " & textAlign(rs.Fields("FSTNME"), 17) & " " & textAlign(rs.Fields("present"), 22) & " " & textAlign(rs.Fields("Remarks"), 5) & vbCrLf [/red]

[blue] rs.MoveNext
Loop

End If
rs.Close
Set rs = Nothing


'***creates and sends email
With objEmail
.To = "me2@somewhere.com"
.CC = "me3@somewhere.com"
If Not IsNull(txtemail) Then
End If
.Subject = "Attendance Roster"
.Body = strtext
.Display


End With


Set objEmail = Nothing
'****closes Outlook. remove if you do not want to close Outlook


End Sub [/blue]

 

[ThomasLafferty]

Try this:

      strBody = strBody & textAlign(rs.Fields("emp#"), 0) & "  " & textAlign(rs.Fields("LSTNME"), 15) & "   " & textAlign(rs.Fields("FSTNME"), 17) & "   " & textAlign(rs.Fields("present"), 22) & "   " & [!]Nz([/!]textAlign(rs.Fields("Remarks"), 5)[!])[/!] & vbCrLf

Add the text in red - the Nz function keeps a null from propagating through an expression.

Hope this does the trick...

Tom

Born once die twice; born twice die once.

 

[serino]

ThomasLasfferty,


Tried that, still receiving the error.

 

[Remou]

How about:

strBody = <...> & (textAlign(Trim(rs.Fields("Remarks") & " "), 5)) & vbCrLf

Or

strBody = <...> & (textAlign(Nz(rs.Fields("Remarks")), 5)) & vbCrLf


I think you will need to do the same thing for each field.

 

[serino]

Thanks Remou! That worked.

I only had to add it to the remarks field:

(textAlign(Nz(rs.Fields("Remarks")), 5)) & vbCrLf

 

[Remou]

A null surname or firstname may pop-up at some stage, next time, perhaps, hence the suggestion.

 

[PHV]

I'd change the function for a definitive solution ...
Public Function textAlign(varText As Variant, intColumnWidth As Integer) As String
textAlign = Left(varText & Space(intColumnWidth), intColumnWidth)
End Function


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[serino]

Remou, thank you for suggestion.

PHV..I applied your suggestion by adding the public function and this worked out great.

You all do a stupendous job! Thank you all for your help.