[AccessD] Blank line after concat

jack drawbridge jackandpat.d at gmail.com
Sun Aug 12 10:27:51 CDT 2018


You are welcome. Happy to help.

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

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list