|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 13th, 2009, 07:14 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Carriage return grrrrrr
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 
|
|
#2
December 13th, 2009, 04:53 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Carriage return grrrrrr
"Sue Compelling" wrote in message
... 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 IIF() ?
|
|
#3
December 14th, 2009, 12:34 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Carriage return grrrrrr
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 wrote: 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]
|
|
#4
December 14th, 2009, 03:05 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Carriage return grrrrrr
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" wrote: 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 wrote: 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] .
|
|
#5
December 14th, 2009, 01:49 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Carriage return grrrrrr
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 Compelling wrote: 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]))))
|
|
#6
December 15th, 2009, 01:57 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Carriage return grrrrrr
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 .... ?
|
|
#7
December 16th, 2009, 12:16 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Carriage return grrrrrr
Great - thanks John (and Marshall) - cheers
-- Sue Compelling "John Spencer" wrote: 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 Compelling wrote: 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])))) .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
