[AccessD] Trying to Eliminate white space in a report itdoesn'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],"") = ""