Null values Building Strings

[Novice1]

I'm building a list of e-mail recipients for a recordset using a string. No problem if the field has the data. The problem is when the field is blank in a record. I tried ...

Nz(vRecipientList & rs!SubjectEMail) ... no luck
vRecipientList & Nz(rs!SubjectEMail) ... no luck

Any suggestions would be appreciated.

_______________________

Dim db As DAO.Database
Dim rs As Object
Dim iCount As Integer
Dim rsEmail As DAO.Recordset

Dim vRecipientList As String
Dim vRecipientList2 As String



Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qry61To90DaysFromPDDEmail")


If IsNull([Sender4]) Then

MsgBox "Identify the sender of this e-mail", vbInformation, "Missing Sender"

DoCmd.GoToControl "Sender4"

Else

If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs!RName) Then
vRecipientList = vRecipientList & rs!SubjectEMail & ";"
vRecipientList2 = vRecipientList2 & rs!SupvrEMail & ";"
rs.MoveNext
Else
rs.MoveNext
End If


Loop Until rs.EOF


DoCmd.SendObject acSendNoObject, , , vRecipientList, vRecipientList2, , "PCS Orders Cannot be Published " & vbCr & vbCr & _
"Pending", "Sir/Ma'am," & vbCr & vbCr & "BLUF: Members from your unit, on the attached listing, have not provided us with the necessary documents to prepare their PCS Orders.", True

Else

MsgBox "No one is in the 61 to 90 day window"

End If
End If

 

[ByteMyzer]

You might try the following code revision after the If rs.RecordCount > 0 Then statement:

rs.MoveFirst
[COLOR="Navy"]Do
    If Not[/COLOR] IsNull(rs!RName) [COLOR="Navy"]Then
        If Not[/COLOR] IsNull(rs!SubjectEMail) [COLOR="Navy"]Then[/COLOR]
            vRecipientList = vRecipientList & rs!SubjectEMail & ";"
        [COLOR="Navy"]End If
        If Not[/COLOR] IsNull(rs!SupvrEMail) [COLOR="Navy"]Then[/COLOR]
            vRecipientList2 = vRecipientList2 & rs!SupvrEMail & ";"
        [COLOR="Navy"]End If
    End If[/COLOR]
    rs.MoveNext
[COLOR="Navy"]Loop Until[/COLOR] rs.EOF

 

[arnelgp]

very simple, no need coding.

vRecipientList = vRecipientList & (rs!SubjectEMail + ";")
vRecipientList2 = vRecipientList2 & (rs!SupvrEMail + ";")

 

[Novice1]

I don't understand. Do I place these in my declarative statements, like ...

Dim db As DAO.Database
Dim rs As Object
Dim iCount As Integer
Dim rsEmail As DAO.Recordset

Dim vRecipientList As String
Dim vRecipientList2 As String

vRecipientList = vRecipientList & (rs!SubjectEMail + ";")
vRecipientList2 = vRecipientList2 & (rs!SupvrEMail + ";")

Then

Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qry61To90DaysFromPDDEmail")


If IsNull([Sender4]) Then

MsgBox "Identify the sender of this e-mail", vbInformation, "Missing Sender"

DoCmd.GoToControl "Sender4"

Else

If rs.RecordCount > 0 Then
rs.MoveFirst

Do


DoCmd.SendObject acSendNoObject, , , vRecipientList, vRecipientList2, , "PCS Orders Cannot be Published " & vbCr & vbCr & _
"Pending", "Sir/Ma'am," & vbCr & vbCr & "BLUF: Members from your unit, on the attached listing, have not provided us with the necessary documents to prepare their PCS Orders.", True

Loop Until rs.EOF

Else

MsgBox "No one is in the 61 to 90 day window"

End If
End If

 

[arnelgp]

put it on your code on your fist post:

Dim db As DAO.Database
Dim rs As Object
Dim iCount As Integer
Dim rsEmail As DAO.Recordset

Dim vRecipientList As String
Dim vRecipientList2 As String



Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qry61To90DaysFromPDDEmail")


If IsNull([Sender4]) Then

MsgBox "Identify the sender of this e-mail", vbInformation, "Missing Sender"

DoCmd.GoToControl "Sender4"

Else

If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs!RName) Then
vRecipientList = vRecipientList & (rs!SubjectEMail + ";")
vRecipientList2 = vRecipientList2 & (rs!SupvrEMail + ";")
rs.MoveNext
Else
rs.MoveNext
End If


Loop Until rs.EOF


DoCmd.SendObject acSendNoObject, , , vRecipientList, vRecipientList2, , "PCS Orders Cannot be Published " & vbCr & vbCr & _
"Pending", "Sir/Ma'am," & vbCr & vbCr & "BLUF: Members from your unit, on the attached listing, have not provided us with the necessary documents to prepare their PCS Orders.", True

Else

MsgBox "No one is in the 61 to 90 day window"

End If
End If