Run code if a data field is empty

[mchlle]

I have this code that will run as long as all the fields below have
data in them. How can I get the code to run if at least one field has
data in it.
strBCCEmail = "1" & [SupervisorFax] & "@faxmail.com; " &
[SupervisorEmail] & "; " & "1" & [HRContactFax] & "@faxmail.com; " &
[HRContactEmail]

Thanks

[Larry Linson]

What happens when one of the fields does not have data? What is the context
of your VBA? It appears you are setting a variable from something, which
could be Controls on a Form or Report, or Fields in the underlying Record
Source. What type of data are the Fields? You might use the NZ function if
you are having a problem with Nulls, but it may or may not yield a useful
result.

But, just as an item of information, the code will execute whether or not
there's data in the referenced items... it may encounter an error, or it
might even crash Access, but it will "run".

Larry Linson
Microsoft Access MVP

"mchlle" <mc****@austin.rr.comwrote in message
news:11*********************@73g2000cwn.googlegrou ps.com...

>I have this code that will run as long as all the fields below have
data in them. How can I get the code to run if at least one field has
data in it.
strBCCEmail = "1" & [SupervisorFax] & "@faxmail.com; " &
[SupervisorEmail] & "; " & "1" & [HRContactFax] & "@faxmail.com; " &
[HRContactEmail]

Thanks

[mchlle]

I suppose you are correct it does run and it encounters an error:
Runtime 2295
Unknown message recipient(s); the message was not sent.

I'm trying to send out emails that include the supervisor's email and
fax number, HR's email and fax number of the student's record. I'm
trying to string all the email and fax number fields and put them in
the bcc field of an email message.

It works fine if all four fields have data in them. It opens an email
message from Outlook and inserts all the fields into the bcc field but
if I only have one email or fax number per say I get the above error
message.

Thanks

[Larry Linson]

"mchlle" <mc****@austin.rr.comwrote

I suppose you are correct it does run and it encounters an error:
Runtime 2295
Unknown message recipient(s); the message was not sent.

I'll venture that not only does it run, it runs quite correctly. It is using
the fields to construct the address to which the information is to be sent,
and if any of them are "empty", "null", or "zero-length string", the
resultant address will not be properly filled in -- it will be incomplete.

It's not clear to me whether nothing is being sent to any addressee, or if
all but the improper address (the one for which the field was empty) were
sent. Access is not the problem, if indeed there is a problem -- it is your
mailing / faxing software.

But, you could fix the problem in Access with a little VBA code -- construct
each of the addresses separately, and only if all the parts are there,
concatenate the address to the others for insertion into the mail / fax
message.

Larry Linson
Microsoft Access MVP

[rwr]

mchlle wrote:

I have this code that will run as long as all the fields below have
data in them. How can I get the code to run if at least one field has
data in it.
strBCCEmail = "1" & [SupervisorFax] & "@faxmail.com; " &
[SupervisorEmail] & "; " & "1" & [HRContactFax] & "@faxmail.com; " &
[HRContactEmail]

Thanks

Look at the IsEmpty(), IsNull() and IsBlank() instructions.

The [SupervisorFax], [SupervisorEmail], [HRContactFax], and
[HRContactEmail] fields are the only variables or unknowns you have.
Everything else is a constants. So you could do something like:

If Not (IsNull([SupervisorFax]) or IsEmpty([SupervisorFax]) or _
IsBlank([SupervisorFax]) or [SupervisorFax]="" or _
[SupervisorFax]=" ") then
Supervisor Fax number is good
EndIf

This is just an example, you may not need to use all of these tests
to solve your problem. If any of the above tests return true, then
you cannot create a proper email address.

HTH,
Ron