Carriage return grrrrrr

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

Sue Compelling

Hi All

I've got myself horribly lost with my query - which potentially has 5 rows -

I only want to have a new line if the field has a value - though I end up
with gaps all over the place - no matter how well I butcher this code below
....

If you can help can you pls explain what I'm doing wrong with the + and &
(and should they go before or after the field that you want to check for a
value etc

TIA ...

VolDetails:
((Format([homephone],"""Home: (""@@) @@@-@@@@")))+Chr(13) & Chr(10)+
(Format([workphone],"""Work: (""@@) @@@-@@@@"))+Chr(13) & Chr(10)+
[workextension]+Chr(13) & Chr(10)+
(Format([faxnumber],"""Fax: (""@@) @@@-@@@@"))+Chr(13) & Chr(10)+
(Format([mobilephone],"!""Mobile: (""@@@) @@@-@@@@"))+Chr(13) & Chr(10)+
[emailname]
 
Sue Compelling said:
Hi All

I've got myself horribly lost with my query - which potentially has 5
rows -

I only want to have a new line if the field has a value - though I end up
with gaps all over the place - no matter how well I butcher this code
below
...

If you can help can you pls explain what I'm doing wrong with the + and &
(and should they go before or after the field that you want to check for a
value etc

TIA ...

VolDetails:
((Format([homephone],"""Home: (""@@) @@@-@@@@")))+Chr(13) & Chr(10)+
(Format([workphone],"""Work: (""@@) @@@-@@@@"))+Chr(13) & Chr(10)+
[workextension]+Chr(13) & Chr(10)+
(Format([faxnumber],"""Fax: (""@@) @@@-@@@@"))+Chr(13) & Chr(10)+
(Format([mobilephone],"!""Mobile: (""@@@) @@@-@@@@"))+Chr(13) & Chr(10)+
[emailname]
Click to expand...

IIF() ?
 
Format will turn null into a zero-length string.

Try something like the following. If this works then add the next bit on and
keep adding bits until you have the desired result.

IIF(HomePhone is not Null,Format(HomePhone,"..."),Null) &
(Chr(13) + Chr(10) + IIF(WorkPHone is not Null,Format(WorkPhone,"...),Null)





John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks alot John - worked a treat ...

Only thing left is how do I get access to prevent the carriage return on the
first row [HomePhone] if it is Null?



VolDetails: IIf([HomePhone] Is Not Null,Format([homephone],"""Home: (""@@)
@@@-@@@@"),Null) & (Chr(13) & Chr(10)+IIf([WorkPhone] Is Not
Null,Format([workphone],"""Work: (""@@) @@@-@@@@"),Null) & (Chr(13) &
Chr(10)+[workextension] & Chr(13) & Chr(10)+IIf([faxnumber] Is Not
Null,Format([faxnumber],"""Fax: (""@@) @@@-@@@@"),Null) & (Chr(13) &
Chr(10)+IIf([mobilephone] Is Not Null,Format([mobilephone],"""Mobile:
(""@@@) @@@-@@@@"),Null) & (Chr(13) & Chr(10)+[emailname]))))
--
Sue Compelling


John Spencer said:
Format will turn null into a zero-length string.

Try something like the following. If this works then add the next bit on and
keep adding bits until you have the desired result.

IIF(HomePhone is not Null,Format(HomePhone,"..."),Null) &
(Chr(13) + Chr(10) + IIF(WorkPHone is not Null,Format(WorkPhone,"...),Null)





John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Sue said:
Hi All

I've got myself horribly lost with my query - which potentially has 5 rows -

I only want to have a new line if the field has a value - though I end up
with gaps all over the place - no matter how well I butcher this code below
...

If you can help can you pls explain what I'm doing wrong with the + and &
(and should they go before or after the field that you want to check for a
value etc

TIA ...

VolDetails:
((Format([homephone],"""Home: (""@@) @@@-@@@@")))+Chr(13) & Chr(10)+
(Format([workphone],"""Work: (""@@) @@@-@@@@"))+Chr(13) & Chr(10)+
[workextension]+Chr(13) & Chr(10)+
(Format([faxnumber],"""Fax: (""@@) @@@-@@@@"))+Chr(13) & Chr(10)+
(Format([mobilephone],"!""Mobile: (""@@@) @@@-@@@@"))+Chr(13) & Chr(10)+
[emailname]
Click to expand...
.
Click to expand...
 
You could add carriage return line feed to the beginning of every line and
then trim the first one off using the mid function.

MID(IIF(HomePhone is Null,"", Chr(13) & Chr(10) & Format(HomePhone,"..."))
& IIF(WorkPhone is Null,"", Chr(13) & Chr(10) & Format(WorkPhone,"..."))
& IIF(FaxNumber is Null,"", Chr(13) & Chr(10) & Format(FaxNumber,"..."))
& IIF(MobilePhone is Null,"", Chr(13) & chr(10) & Format(MobilePhone,"..."))
& IIF(EmailName is Null,"", Chr(13) & Chr(10)EmailName)),3)

(Trick I learned from Marshall Barton)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks alot John - worked a treat ...

Only thing left is how do I get access to prevent the carriage return on the
first row [HomePhone] if it is Null?



VolDetails: IIf([HomePhone] Is Not Null,Format([homephone],"""Home: (""@@)
@@@-@@@@") & (Chr(13) & Chr(10),Null) +IIf([WorkPhone] Is Not ....

?
 
Great - thanks John (and Marshall) - cheers
--
Sue Compelling


John Spencer said:
You could add carriage return line feed to the beginning of every line and
then trim the first one off using the mid function.

MID(IIF(HomePhone is Null,"", Chr(13) & Chr(10) & Format(HomePhone,"..."))
& IIF(WorkPhone is Null,"", Chr(13) & Chr(10) & Format(WorkPhone,"..."))
& IIF(FaxNumber is Null,"", Chr(13) & Chr(10) & Format(FaxNumber,"..."))
& IIF(MobilePhone is Null,"", Chr(13) & chr(10) & Format(MobilePhone,"..."))
& IIF(EmailName is Null,"", Chr(13) & Chr(10)EmailName)),3)

(Trick I learned from Marshall Barton)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Sue said:
Thanks alot John - worked a treat ...

Only thing left is how do I get access to prevent the carriage return on the
first row [HomePhone] if it is Null?



VolDetails: IIf([HomePhone] Is Not Null,Format([homephone],"""Home: (""@@)
@@@-@@@@"),Null) & (Chr(13) & Chr(10)+IIf([WorkPhone] Is Not
Null,Format([workphone],"""Work: (""@@) @@@-@@@@"),Null) & (Chr(13) &
Chr(10)+[workextension] & Chr(13) & Chr(10)+IIf([faxnumber] Is Not
Null,Format([faxnumber],"""Fax: (""@@) @@@-@@@@"),Null) & (Chr(13) &
Chr(10)+IIf([mobilephone] Is Not Null,Format([mobilephone],"""Mobile:
(""@@@) @@@-@@@@"),Null) & (Chr(13) & Chr(10)+[emailname]))))
Click to expand...
.
Click to expand...
 
Back
Top