Carriage return grrrrrr

[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]

 

[David F Cox]

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]

IIF() ?

 

[John Spencer]

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 Compelling]

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

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

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]

.

 

[John Spencer]

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

 

[David F Cox]

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

?

 

[Sue Compelling]

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

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: (""@@)
@@@-@@@@"),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]))))

.