[AccessD] Blank line after concat

[Kostas Konstantinidis]

Great,
That works

Many thank's

-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of jack
drawbridge
Sent: Sunday, August 12, 2018 6:03 PM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Blank line after concat

Try this one

SELECT Kostas.id
, IIf(IsNull([International Title]),[original title],[International Title])
&
IIf(IsNull([director]),"",Chr(13) & Chr(10) & "Directed by: " &[director])
  &
IIf(IsNull([DIR_TEL]),"",  Chr(13) & Chr(10) & "Tel:"  &[DIR_TEL]
) &
IIf(IsNull([DIR_MAIL]),"", Chr(13)& Chr(10)& "e-mail: " &
[DIR_MAIL])
 AS ID_DIR
FROM kostas;

On Sun, Aug 12, 2018 at 10:08 AM, Kostas Konstantinidis <kost36 at gmail.com>
wrote:

> Dear Jack,
> It's the same I am trying to make it to work on Look more carefully to 
> the 4th record
>
> e-mail field is null
> and there is a blank line to the end
>
> Thank's for your time
>
> /kostas
>
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of jack 
> drawbridge
> Sent: Sunday, August 12, 2018 3:49 PM
> To: Access Developers discussion and problem solving 
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Blank line after concat
>
> Kostas,
> Here is an attempt. My default delimiter is comma(,) not semicolon(;), 
> so I have adjusted the sql and used some tet data as below.
> Hope it arrives without too much weird formatting and that it is 
> helpful to you.
>
> *Kostas*
> id International Title original title director DIR_TEL DIR_MAIL
> 1 InterAgent Agent Bob Loblaw 6132784623 bob at gmail.com
> 2
> Chairman
> 6132784623 robert at gmail.net
> 3 Intl Chief
> Brian G
> d at cox.net
> 4
> Regular Ron Old Roy 2556791234
> 5 Vicepresidente internacional Leon Gomez Mikel Hernandez 
> mhernandez at swiftnet.org
>
> My SQL:
>
> SELECT Kostas.id
> , IIf(IsNull([International Title]),[original title],[International 
> Title]) & Chr(13) & Chr(10) & IIf(IsNull([director]),"",("Directed by: "
> &[director])  & Chr(13) & Chr(10)) & IIf(IsNull([DIR_TEL]),"","Tel:"
> &[DIR_TEL] & Chr(13)& Chr(10)) &
> IIf(IsNull([DIR_MAIL]),"",("e-mail: " &
> [DIR_MAIL]))
>  AS ID_DIR
> FROM kostas;
>
> Gives Result
>
> *qkostas*
> id ID_DIR
> 1 InterAgent
> Directed by: Bob Loblaw
> Tel:6132784623
> e-mail: bob at gmail.com
> 2 Chairman
> Tel:6132784623
> e-mail: robert at gmail.net
> 3 Intl Chief
> Directed by: Brian G
> e-mail: d at cox.net
> 4 Regular Ron
> Directed by: Old Roy
> Tel:2556791234
> 5 Vicepresidente internacional
> Directed by: Mikel Hernandez
> e-mail: mhernandez at swiftnet.org
>
>
> >
> > -----Original Message-----
> > From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of 
> > Stuart McLachlan
> > Sent: Sunday, August 12, 2018 12:38 PM
> > To: Access Developers discussion and problem solving 
> > <accessd at databaseadvisors.com>
> > Subject: Re: [AccessD] Blank line after concat
> >
> > Get rid of the + and just use &.  Those two operators work 
> > differently with Nulls.
> >
> > ? "A" & Null  & "B"
> > returns "AB"
> >
> > ? "A" + Null + "B"
> > returns Null
> >
> >
> > On 12 Aug 2018 at 11:49, Kostas Konstantinidis wrote:
> >
> > > Dear people,
> > > In a query I use
> > >
> > > ID_DIR: (IIf(IsNull([International Title]);[original 
> > > title];[International Title])+Chr(13)+Chr(10)) & 
> > > (IIf(IsNull([director]);"";("Directed by: " &
> > > [director])+Chr(13)+Chr(10))) & (IIf(IsNull([DIR_TEL]);"";"Tel:
> > > "+([DIR_TEL]) + Chr(13)+Chr(10))) &
> > > (IIf(IsNull([DIR_MAIL]);"";("e-mail: " & [DIR_MAIL])))
> > >
> > > The problem is that if any of [Director], [DIR_TEL[ or [DIR_MAIL] 
> > > fields are null it always returns a blank line to the end
> > >
> > > Could you please help?
> > >
> > > Thank's
> > > /kostas
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com