Address

[Pauldohert]

What the best way of combining fields to get a one field output for addresses

ie Address1, 2, 3, 4, 4 Town, Postcode where the 2, 3, 4 could be '' or null , so when the final field is formatted - the null ines have been removed.

At the moment all I have is long case statements which I can't see as being the best way.

Thanks

 

[GaryPanic]

This is what I do


Addr: [InsAdd1] & IIf(IsNull([InsAdd2]),"",Chr(13) & Chr(10) & [InsAdd2]) & IIf(IsNull([InsAdd3]),"",Chr(13) & Chr(10) & [InsAdd3]) & IIf(IsNull([InsAdd4]),"",Chr(13) & Chr(10) & [InsAdd4]) & IIf(IsNull([InsPostCode]),"",Chr(13) & Chr(10) & [InsPostCode])


and seems to work bit of a pig - but once done - I just copy it and paste where ever I need it


hth
g

 

[Pauldohert]

Thanks Gary - I meant with SQL doing the work not access.

Ta

 

[GaryPanic]

I'll keep quite - not my bag
g

 

[SQL_Hell]

Hi there,

I can think of a way to get round the nulls easily

select isnull(add_1,''), isnull(add_2,'')
from mytable


How come the address fields can be null or ' '?

 

[Pauldohert]

Thanks but whats the best way to then join it together - so the '' lines don't show.


Data comes from lots of sources so I think '' and null is allowed in the table.

Thanks

 

[SQL_Hell]

personally I would update all fields to null before hand, or maybe even handle it when the data comes in, then you dont have to try and program around two possible conditions.

so...

update my table
set add_1 = null,
set add_2 =null
where add_1 = ' ' and add_2 = ' '

then...

select isnull(add_1,'') + ' ' + isnull(add_2,'') + ' '
from mytable

 

[Pauldohert]

Thanks - but that still leaves me with spaces following a null or '' address line - which is what I am trying to avoid.

 

[FoFa]

In T-SQL (Sql Server SQL) you can use it like this
ISNULL(Address2 + CHAR(13)),'')
THEN if Address2 is NULL, the CHAR(13) would be dropped (String concatenated to NULL results in a NULL), leaving the ''
This would get rid of your blank lines.
But if Address2 was NOT NULL, then it append the CR to the end of it like normal.

 

[SQL_Hell]

ok sorry, I didn't realise my mistake I'm a bit slow after the weekend

ok how about this

CASE ISNULL(LTRIM(ADD_1),'') WHEN '' THEN '' ELSE ADD_1 + ' ' END +
CASE ISNULL(LTRIM(ADD_2),'') WHEN '' THEN '' ELSE ADD_2 + ' ' END +
CASE ISNULL(LTRIM(ADD_3),'') WHEN '' THEN '' ELSE ADD_3 + ' ' END +
CASE ISNULL(LTRIM(ADD_4),'') WHEN '' THEN '' ELSE ADD_4 + ' ' END +
CASE ISNULL(LTRIM(POST_CODE),'') WHEN '' THEN '' ELSE POST_CODE END AS ONELINEADDRESS,


I know this a case statement, but i cant see any other way round it

 

[Pauldohert]

OK cheers.

 

[SQL_Hell]

In T-SQL (Sql Server SQL) you can use it like this
ISNULL(Address2 + CHAR(13)),'')
THEN if Address2 is NULL, the CHAR(13) would be dropped (String concatenated to NULL results in a NULL), leaving the ''
This would get rid of your blank lines.
But if Address2 was NOT NULL, then it append the CR to the end of it like normal.

Ok that's even better, I am surpised this works though because I would have thought address2 + char(13) would render it not null. But it does work....I just tested it.

 

[Pauldohert]

In T-SQL (Sql Server SQL) you can use it like this
ISNULL(Address2 + CHAR(13)),'')
THEN if Address2 is NULL, the CHAR(13) would be dropped (String concatenated to NULL results in a NULL), leaving the ''
This would get rid of your blank lines.
But if Address2 was NOT NULL, then it append the CR to the end of it like normal.

Thanks - I knew something like this worked for what I wanted.

Cheers

 

[FoFa]

Ok that's even better, I am surpised this works though because I would have thought address2 + char(13) would render it not null. But it does work....I just tested it.

And you doubted me?

Unless you have one of those server setting to disallow it, the default is any string concatenation to a NULL value results in a NULL value.
SO NULL + 'This is a Test' result in a NULL
Also "This ' + 'is ' + 'a ' + 'test ' + NULL results in a NULL value
Hence the reason to check for NULLs in a string concatenation as
ISNULL('This is a test','') + ISNULL(SomeColumn,'') just in case. But you can also use it to your advantage as in the script this thread is based on.