[AccessD] Blank line after concat

Kostas Konstantinidis kost36 at gmail.com
Sun Aug 12 09:08:08 CDT 2018


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



More information about the AccessD mailing list