Removing Empty Line

  • Thread starter Thread starter Sue Compelling
  • Start date Start date
S

Sue Compelling

Hi

I was given the following code (via this forum) to remove the first empty
line if my client doesn't have a home telephone #. I works brilliantly - but
I now realise I have situations where a client will only have an email
address (or a mobile phone)and this gets presented with an empty row in the
report. I've tried multiple variations to the below though none give me the
result I need.

Hoping someone can help.

=Mid(IIf([HomePhone] Is Null,"",Chr(13) & Chr(10) &
Format([homephone],"""Home: (""@@) @@@-@@@@")) & IIf([WorkPhone] Is
Null,"",Chr(13) & Chr(10) & Format([workphone],"""Work: (""@@) @@@-@@@@")) &
(Chr(13) & Chr(10)+[workextension] & IIf([FaxNumber] Is Null,"",Chr(13) &
Chr(10) & Format([faxnumber],"""Fax: (""@@) @@@-@@@@")) & IIf([MobilePhone]
Is Null,"",Chr(13) & Chr(10) & Format([mobilephone],"""Mobile: (""@@@)
@@@-@@@@")) & IIf([EmailName] Is Null,"",Chr(13) & Chr(10)+"Email: " &
[EmailName])),3)
 
When an expression gets that convoluted, I defer to creating a small
user-defined function. Try open a new, blank module and paste this function
into it. Save the module as "modStringFunctions". The function will allow you
to easily use this code again and again and again and again....

You can call the function in your report by setting a control source to:

=PhonesEmail([HomePhone], [WorkPhone], [WorkExtension], [FaxNumber],
[MobilePhone], [EmailName],"(@@) @@@-@@@@","(@@@) @@@-@@@@")

'code begins here
Public Function PhonesEmail(varHome As Variant, _
varWork As Variant, varWorkExt As Variant, _
varFax As Variant, varMobile As Variant, _
varEmail As Variant, Optional strPhoneFormat As String, _
Optional strMobileFormat As String) As String
Dim strReturn As String
If Not IsNull(varHome) Then
strReturn = strReturn & "Home: " & Format(varHome, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varWork) Then
strReturn = strReturn & "Work: " & Format(varWork, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varWorkExt) Then
strReturn = strReturn & varWorkExt & vbCrLf
End If
If Not IsNull(varFax) Then
strReturn = strReturn & "Fax: " & Format(varFax, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varMobile) Then
strReturn = strReturn & "Mobile: " & Format(varWork,
strMobileFormat) & vbCrLf
End If
If Not IsNull(varEmail) Then
strReturn = strReturn & "Email: " & varEmail & vbCrLf
End If
If Len(strReturn) > 2 Then
PhonesEmail = Left(strReturn, Len(strReturn) - 2)
Else
PhonesEmail = ""
End If
End Function
'code ends here
 
Hi Duane -

Nearly there - if my client has a mobile - the mobile number is not showing
up, ie - the details appear like ...:

Home: (09) 412 5678
Mobile:
Email: (e-mail address removed)

Thanks for your help so far ...

--
Sue Compelling


Duane Hookom said:
When an expression gets that convoluted, I defer to creating a small
user-defined function. Try open a new, blank module and paste this function
into it. Save the module as "modStringFunctions". The function will allow you
to easily use this code again and again and again and again....

You can call the function in your report by setting a control source to:

=PhonesEmail([HomePhone], [WorkPhone], [WorkExtension], [FaxNumber],
[MobilePhone], [EmailName],"(@@) @@@-@@@@","(@@@) @@@-@@@@")

'code begins here
Public Function PhonesEmail(varHome As Variant, _
varWork As Variant, varWorkExt As Variant, _
varFax As Variant, varMobile As Variant, _
varEmail As Variant, Optional strPhoneFormat As String, _
Optional strMobileFormat As String) As String
Dim strReturn As String
If Not IsNull(varHome) Then
strReturn = strReturn & "Home: " & Format(varHome, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varWork) Then
strReturn = strReturn & "Work: " & Format(varWork, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varWorkExt) Then
strReturn = strReturn & varWorkExt & vbCrLf
End If
If Not IsNull(varFax) Then
strReturn = strReturn & "Fax: " & Format(varFax, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varMobile) Then
strReturn = strReturn & "Mobile: " & Format(varWork,
strMobileFormat) & vbCrLf
End If
If Not IsNull(varEmail) Then
strReturn = strReturn & "Email: " & varEmail & vbCrLf
End If
If Len(strReturn) > 2 Then
PhonesEmail = Left(strReturn, Len(strReturn) - 2)
Else
PhonesEmail = ""
End If
End Function
'code ends here
--
Duane Hookom
Microsoft Access MVP


Sue Compelling said:
Hi

I was given the following code (via this forum) to remove the first empty
line if my client doesn't have a home telephone #. I works brilliantly - but
I now realise I have situations where a client will only have an email
address (or a mobile phone)and this gets presented with an empty row in the
report. I've tried multiple variations to the below though none give me the
result I need.

Hoping someone can help.

=Mid(IIf([HomePhone] Is Null,"",Chr(13) & Chr(10) &
Format([homephone],"""Home: (""@@) @@@-@@@@")) & IIf([WorkPhone] Is
Null,"",Chr(13) & Chr(10) & Format([workphone],"""Work: (""@@) @@@-@@@@")) &
(Chr(13) & Chr(10)+[workextension] & IIf([FaxNumber] Is Null,"",Chr(13) &
Chr(10) & Format([faxnumber],"""Fax: (""@@) @@@-@@@@")) & IIf([MobilePhone]
Is Null,"",Chr(13) & Chr(10) & Format([mobilephone],"""Mobile: (""@@@)
@@@-@@@@")) & IIf([EmailName] Is Null,"",Chr(13) & Chr(10)+"Email: " &
[EmailName])),3)
Click to expand...
Click to expand...
 
Also Duane

On closer inspection ... if the client has a work phone and a mobile phone
the work phone number is appearing twice - once in work phone and then in mob
phone (with mob phone formatting) ... like so ....

Home: (09) xxx-xxxx
Work: (09) 123-4567
Mobile ( 09) 123-4567

Cheers
--
Sue Compelling


Duane Hookom said:
When an expression gets that convoluted, I defer to creating a small
user-defined function. Try open a new, blank module and paste this function
into it. Save the module as "modStringFunctions". The function will allow you
to easily use this code again and again and again and again....

You can call the function in your report by setting a control source to:

=PhonesEmail([HomePhone], [WorkPhone], [WorkExtension], [FaxNumber],
[MobilePhone], [EmailName],"(@@) @@@-@@@@","(@@@) @@@-@@@@")

'code begins here
Public Function PhonesEmail(varHome As Variant, _
varWork As Variant, varWorkExt As Variant, _
varFax As Variant, varMobile As Variant, _
varEmail As Variant, Optional strPhoneFormat As String, _
Optional strMobileFormat As String) As String
Dim strReturn As String
If Not IsNull(varHome) Then
strReturn = strReturn & "Home: " & Format(varHome, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varWork) Then
strReturn = strReturn & "Work: " & Format(varWork, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varWorkExt) Then
strReturn = strReturn & varWorkExt & vbCrLf
End If
If Not IsNull(varFax) Then
strReturn = strReturn & "Fax: " & Format(varFax, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varMobile) Then
strReturn = strReturn & "Mobile: " & Format(varWork,
strMobileFormat) & vbCrLf
End If
If Not IsNull(varEmail) Then
strReturn = strReturn & "Email: " & varEmail & vbCrLf
End If
If Len(strReturn) > 2 Then
PhonesEmail = Left(strReturn, Len(strReturn) - 2)
Else
PhonesEmail = ""
End If
End Function
'code ends here
--
Duane Hookom
Microsoft Access MVP


Sue Compelling said:
Hi

I was given the following code (via this forum) to remove the first empty
line if my client doesn't have a home telephone #. I works brilliantly - but
I now realise I have situations where a client will only have an email
address (or a mobile phone)and this gets presented with an empty row in the
report. I've tried multiple variations to the below though none give me the
result I need.

Hoping someone can help.

=Mid(IIf([HomePhone] Is Null,"",Chr(13) & Chr(10) &
Format([homephone],"""Home: (""@@) @@@-@@@@")) & IIf([WorkPhone] Is
Null,"",Chr(13) & Chr(10) & Format([workphone],"""Work: (""@@) @@@-@@@@")) &
(Chr(13) & Chr(10)+[workextension] & IIf([FaxNumber] Is Null,"",Chr(13) &
Chr(10) & Format([faxnumber],"""Fax: (""@@) @@@-@@@@")) & IIf([MobilePhone]
Is Null,"",Chr(13) & Chr(10) & Format([mobilephone],"""Mobile: (""@@@)
@@@-@@@@")) & IIf([EmailName] Is Null,"",Chr(13) & Chr(10)+"Email: " &
[EmailName])),3)
Click to expand...
Click to expand...
 
Sue,
You should be able to find my mistakes and correct them.

This code:
If Not IsNull(varMobile) Then
strReturn = strReturn & "Mobile: " & Format(varWork,
strMobileFormat) & vbCrLf
End If

should be:
If Not IsNull(varMobile) Then
strReturn = strReturn & "Mobile: " & Format(varMobile,
strMobileFormat) & vbCrLf
End If

--
Duane Hookom
Microsoft Access MVP


Sue Compelling said:
Also Duane

On closer inspection ... if the client has a work phone and a mobile phone
the work phone number is appearing twice - once in work phone and then in mob
phone (with mob phone formatting) ... like so ....

Home: (09) xxx-xxxx
Work: (09) 123-4567
Mobile ( 09) 123-4567

Cheers
--
Sue Compelling


Duane Hookom said:
When an expression gets that convoluted, I defer to creating a small
user-defined function. Try open a new, blank module and paste this function
into it. Save the module as "modStringFunctions". The function will allow you
to easily use this code again and again and again and again....

You can call the function in your report by setting a control source to:

=PhonesEmail([HomePhone], [WorkPhone], [WorkExtension], [FaxNumber],
[MobilePhone], [EmailName],"(@@) @@@-@@@@","(@@@) @@@-@@@@")

'code begins here
Public Function PhonesEmail(varHome As Variant, _
varWork As Variant, varWorkExt As Variant, _
varFax As Variant, varMobile As Variant, _
varEmail As Variant, Optional strPhoneFormat As String, _
Optional strMobileFormat As String) As String
Dim strReturn As String
If Not IsNull(varHome) Then
strReturn = strReturn & "Home: " & Format(varHome, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varWork) Then
strReturn = strReturn & "Work: " & Format(varWork, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varWorkExt) Then
strReturn = strReturn & varWorkExt & vbCrLf
End If
If Not IsNull(varFax) Then
strReturn = strReturn & "Fax: " & Format(varFax, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varMobile) Then
strReturn = strReturn & "Mobile: " & Format(varWork,
strMobileFormat) & vbCrLf
End If
If Not IsNull(varEmail) Then
strReturn = strReturn & "Email: " & varEmail & vbCrLf
End If
If Len(strReturn) > 2 Then
PhonesEmail = Left(strReturn, Len(strReturn) - 2)
Else
PhonesEmail = ""
End If
End Function
'code ends here
--
Duane Hookom
Microsoft Access MVP


Sue Compelling said:
Hi

I was given the following code (via this forum) to remove the first empty
line if my client doesn't have a home telephone #. I works brilliantly - but
I now realise I have situations where a client will only have an email
address (or a mobile phone)and this gets presented with an empty row in the
report. I've tried multiple variations to the below though none give me the
result I need.

Hoping someone can help.

=Mid(IIf([HomePhone] Is Null,"",Chr(13) & Chr(10) &
Format([homephone],"""Home: (""@@) @@@-@@@@")) & IIf([WorkPhone] Is
Null,"",Chr(13) & Chr(10) & Format([workphone],"""Work: (""@@) @@@-@@@@")) &
(Chr(13) & Chr(10)+[workextension] & IIf([FaxNumber] Is Null,"",Chr(13) &
Chr(10) & Format([faxnumber],"""Fax: (""@@) @@@-@@@@")) & IIf([MobilePhone]
Is Null,"",Chr(13) & Chr(10) & Format([mobilephone],"""Mobile: (""@@@)
@@@-@@@@")) & IIf([EmailName] Is Null,"",Chr(13) & Chr(10)+"Email: " &
[EmailName])),3)
Click to expand...
Click to expand...
Click to expand...
 
Thanks so much Duane. And yes you're right - I should have picked that up.
--
Sue Compelling


Duane Hookom said:
Sue,
You should be able to find my mistakes and correct them.

This code:
If Not IsNull(varMobile) Then
strReturn = strReturn & "Mobile: " & Format(varWork,
strMobileFormat) & vbCrLf
End If

should be:
If Not IsNull(varMobile) Then
strReturn = strReturn & "Mobile: " & Format(varMobile,
strMobileFormat) & vbCrLf
End If

--
Duane Hookom
Microsoft Access MVP


Sue Compelling said:
Also Duane

On closer inspection ... if the client has a work phone and a mobile phone
the work phone number is appearing twice - once in work phone and then in mob
phone (with mob phone formatting) ... like so ....

Home: (09) xxx-xxxx
Work: (09) 123-4567
Mobile ( 09) 123-4567

Cheers
--
Sue Compelling


Duane Hookom said:
When an expression gets that convoluted, I defer to creating a small
user-defined function. Try open a new, blank module and paste this function
into it. Save the module as "modStringFunctions". The function will allow you
to easily use this code again and again and again and again....

You can call the function in your report by setting a control source to:

=PhonesEmail([HomePhone], [WorkPhone], [WorkExtension], [FaxNumber],
[MobilePhone], [EmailName],"(@@) @@@-@@@@","(@@@) @@@-@@@@")

'code begins here
Public Function PhonesEmail(varHome As Variant, _
varWork As Variant, varWorkExt As Variant, _
varFax As Variant, varMobile As Variant, _
varEmail As Variant, Optional strPhoneFormat As String, _
Optional strMobileFormat As String) As String
Dim strReturn As String
If Not IsNull(varHome) Then
strReturn = strReturn & "Home: " & Format(varHome, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varWork) Then
strReturn = strReturn & "Work: " & Format(varWork, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varWorkExt) Then
strReturn = strReturn & varWorkExt & vbCrLf
End If
If Not IsNull(varFax) Then
strReturn = strReturn & "Fax: " & Format(varFax, strPhoneFormat) &
vbCrLf
End If
If Not IsNull(varMobile) Then
strReturn = strReturn & "Mobile: " & Format(varWork,
strMobileFormat) & vbCrLf
End If
If Not IsNull(varEmail) Then
strReturn = strReturn & "Email: " & varEmail & vbCrLf
End If
If Len(strReturn) > 2 Then
PhonesEmail = Left(strReturn, Len(strReturn) - 2)
Else
PhonesEmail = ""
End If
End Function
'code ends here
--
Duane Hookom
Microsoft Access MVP


:

Hi

I was given the following code (via this forum) to remove the first empty
line if my client doesn't have a home telephone #. I works brilliantly - but
I now realise I have situations where a client will only have an email
address (or a mobile phone)and this gets presented with an empty row in the
report. I've tried multiple variations to the below though none give me the
result I need.

Hoping someone can help.

=Mid(IIf([HomePhone] Is Null,"",Chr(13) & Chr(10) &
Format([homephone],"""Home: (""@@) @@@-@@@@")) & IIf([WorkPhone] Is
Null,"",Chr(13) & Chr(10) & Format([workphone],"""Work: (""@@) @@@-@@@@")) &
(Chr(13) & Chr(10)+[workextension] & IIf([FaxNumber] Is Null,"",Chr(13) &
Chr(10) & Format([faxnumber],"""Fax: (""@@) @@@-@@@@")) & IIf([MobilePhone]
Is Null,"",Chr(13) & Chr(10) & Format([mobilephone],"""Mobile: (""@@@)
@@@-@@@@")) & IIf([EmailName] Is Null,"",Chr(13) & Chr(10)+"Email: " &
[EmailName])),3)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top