[AccessD] Trying to Eliminate white space in a report it doesn'twork

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],"") = ""
>
>
>
>
> -- 
> Stuart
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>