[AccessD] Trying to Eliminate white space in a reportitdoesn'twork

Gustav thank's
now it works perfect

/kostas
----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Sunday, June 05, 2005 11:52 AM
Subject: Re: [AccessD] Trying to Eliminate white space in a 
reportitdoesn'twork


> Hi Kostas
>
> Adjust it to:
>
> SELECT
>  RT_AM_production.AM,
>  [Producer] & (" / " + [person]) AS Production,
>  RT_AM_production.PROD_ADDRESS,
>  ([PROD_CODE] + " ") & [town] & (" " + [country]) AS Address2,
>  ("Tel: " + [PROD_TLE]) & (" Fax: " + [PROD_FAX]) AS Communication,
>  RT_AM_production.PROD_E_MAIL
> FROM
>  T_town
> RIGHT JOIN
>  (T_producer RIGHT JOIN
>    (T_person RIGHT JOIN
>      (T_country RIGHT JOIN
>        RT_AM_production
>        ON
>        T_country.ID_country = RT_AM_production.ID_PROD_COUNTRY)
>      ON
>      T_person.ID_person = RT_AM_production.ID_PROD_PERSON)
>    ON
>    T_producer.ID_producer = RT_AM_production.IDproducer)
>  ON
>  T_town.IDDIR_TOWN = RT_AM_production.ID_PROD_TOWN;
>
> This will return Null values for empty fields, and now your code in the
> report will work and can be reduced to this:
>
> =([Production] + Chr(13) & Chr(10)) &
> ([PROD_ADDRESS] + Chr(13) & Chr(10)) &
> ([Address2] + & Chr(13) & Chr(10)) &
> ([Communication] + Chr(13) & Chr(10)) &
> [PROD_E_MAIL]
>
> The plus sign has the effect that (Null + somestring) is still Null.
>
> /gustav
>
>>>> kost36 at otenet.gr 06/05 8:59 am >>>
> Mike & Doris and Stuart thank's a lot for your responses
> here is the query on which the report is based on
>
> SELECT RT_AM_production.AM, IIf(IsNull([Producer]),"",[Producer]) &
> IIf(IsNull([person]),"",(" / " & [person])) AS Production,
> RT_AM_production.PROD_ADDRESS, IIf([PROD_CODE] Is Null,[town] & "" &
> [country],[PROD_CODE] & "" & [town] & "" & [country]) AS Address2,
> IIf(IsNull([PROD_TLE]),"",("Tel: " & [PROD_TLE])) &
> IIf(IsNull([PROD_FAX]),"",(" Fax: " & [PROD_FAX])) AS Communication,
> RT_AM_production.PROD_E_MAIL
> FROM T_town RIGHT JOIN (T_producer RIGHT JOIN (T_person RIGHT JOIN
> (T_country RIGHT JOIN RT_AM_production ON T_country.ID_country =
> RT_AM_production.ID_PROD_COUNTRY) ON T_person.ID_person =
> RT_AM_production.ID_PROD_PERSON) ON T_producer.ID_producer =
> RT_AM_production.IDproducer) ON T_town.IDDIR_TOWN =
> RT_AM_production.ID_PROD_TOWN;
>
> Running it seems to keep no blank spaces into the records.
> The problem must be between fields Address2 and Communication but I
> cant
> understand the real reason
>
> thank's
> / kostas
>
>
> ----- Original Message ----- 
> From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Sent: Sunday, June 05, 2005 3:55 AM
> Subject: Re: [AccessD] Trying to Eliminate white space in a report it
> doesn'twork
>
>
>> On 4 Jun 2005 at 21:58, Kostas Konstantinidis wrote:
>>
>>> Hi all,
>>> in a report I use the follown
>>>
>>> =IIf(IsNull([Production]);"";[Production] & Chr(13) & Chr(10)) &
>>> IIf(IsNull([PROD_ADDRESS]);"";[PROD_ADDRESS] & Chr(13) & Chr(10)) &
>>> IIf(IsNull([Address2]);"";[Address2] & Chr(13) & Chr(10)) &
>>> IIf(IsNull([Communication]);"";[Communication]) & Chr(13) & Chr(10)
> &
>>> IIf(IsNull([PROD_E_MAIL]);"";[PROD_E_MAIL]) & Chr(13) & Chr(10)
>>>
>>> but in case one of  the in between Production and Addess2 records
> are
>>> blank
>>> and PROD_E_MAIL is not, then it seems it doesn't eliminate the
> vertical
>>> space holding white space
>>> am I doing something wrong or it needs to CanShrink the Details
> too?
>>>
>>
>> There is a difference between Null  an empty string  ("").. If you
> are
>> getting a vertical blank line, I'd suspect that one of the fields
> contains
>> an empty string rather than a Null.
>>
>> Try replacing IsNull with NZ([Production],"") = ""
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>