Removing Empty Line

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)
 
D

Duane Hookom

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
 
S

Sue Compelling

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...
 
S

Sue Compelling

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...
 
D

Duane Hookom

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...
 
S

Sue Compelling

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Carriage return grrrrrr 6
Address box with IIf Statement 7
Post Codes 2
Textbox Formatting 3
Hlp Pls 1
Carriage Return in calculated field 7
Concatenate shows "square boxes" unknown characters at paragraph e 3
IIf Statement returns Text when null 1

Top