[AccessD] Gaps in Address Query

On 15 Apr 2003 at 11:56, paul.hartland at fsmail.net wrote:

> 
> AddressDetail: [Name] & Chr(13) &
> Chr(10)+IIf(IsNull([addrs1]),"",[addrs1]+Chr(13)+Chr(10))+IIf(IsNull([
> addrs2]),"",[addrs2]+Chr(13)+Chr(10))+IIf(IsNull([town]),"",[town]+Chr
> (13)+Chr(10))+IIf(IsNull([county]),"",[county]+Chr(13)+Chr(10))+[postc
> ode]
> 
> Basically, if a part of the address line is null then I want to shift
> the address up, if not put the field in and move onto the next line. 
> The query runs ok, however there are still line gaps in the address
> where the field is blank.  I have tried various combinations including
> changing the + to & and only using + for the chr(13)+chr(10), and the
> query still comes out the same.

I would suspect that some of the fields contain empty strings rather 
than Nulls. I've seen this quite  often happening during data 
entry/editing.   An empty string ("") is not the same as a Null.

Try IIf(NZ([addrs1],"") = ,"",[addrs1]+Chr(13)+Chr(10))

This will convert all Nulls to empty strings so you will catch both.






-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.