NullToZero

[RobInAZ]

I have a database that stores expiration dates of department charge numbers. The database emails personnel, that I enter into each record, at 15 and 30 days prior to expiration using SendObject. I have a total of three fields where I enter an email address for each record; Email, E2, and E3. If I enter data in Email and E2 an email is sent. If I enter data in Email,E2, and E3 an email is sent. The problem happens when I only enter data in Email, I get an error 2295. I'm sure that it has something to do with the below piece of code and my use of Nz:

strRecipient = rs!Email & (Nz(";" & (rs!E2))) & (Nz(";" & (rs!E3)))

Any help would be greatly appreciated.

- Rob

 

[Galaxiom]

Try this:

strRecipient = rs!Email & Iif(IsNull(rs!E2),"",";" & rs!E2) & Iif(IsNull(rs!E3),"",";" & rs!E3)

 

[RobInAZ]

Thank you it worked.

One other question.
What would the code look like to allow entry in any of the three fields: Email and/or E2 and/or E3

 

[Galaxiom]

I didn't have time to mention this yesterday but having three fields for email is not really the ideal solution.

They should be held in a related table with one record for each email. This allows any number of records to be entered and there are no Nulls. They are displayed on a form in a subform.

They are added to the email using a loop through a recordset. This code is actually simpler because it doesn't have to test for Nulls. Each record can just be added followed by a semicolon.

 

[RobInAZ]

Thank you again for the help.
I really appreciate it.