That actually works better that the stored procedures for SQL server databases, but it doesn't work for other odbc databases (eg Access).  Is there a generic odbc command that will...

Thanks for all your help.  I found out that the reason it wasn't working was that I needed to trim the output of the qfields recordset!

Now that it's all working fine, I...

I think one thing guaranteed if you want to get the 1st section of the postcode is that the last 3 digits always make up the 2nd section, therefore if...

In the end my trigger has the following:

UPDATE address

set addr_region = (select pcode_region from postcoderegions where pcode_prefix = left(addr_postcode, 2)) where left(addr_postcode,2) like '[a-z][a-z]%'

UPDATE address

set addr_region = (select pcode_region from...

In the end my trigger has the following:

UPDATE address

set addr_region = (select pcode_region from postcoderegions where pcode_prefix = left(addr_postcode, 2)) where left(addr_postcode,2) like '[a-z][a-z]%'

UPDATE address

set addr_region = (select pcode_region from...

In the end my trigger has the following:

UPDATE address

set addr_region = (select pcode_region from postcoderegions where pcode_prefix = left(addr_postcode, 2)) where left(addr_postcode,2) like '[a-z][a-z]%'

UPDATE address

set addr_region = (select pcode_region from...

You can't work on the 1st 3 characters being the 1st part as some have 4!  An example being WC1V.

You can always trust the post office to create a logical...

Thanks for that.  All works fine now.

Hi,

I did make that amendment, I was just saying that I need to clean up the data somewhat.  Thanks for your help.

Much appreciated,

James Knight

You were right, there were line feeds as well.

UPDATE address

   SET addr_address1 = dbo.fGetTokenN(addr_address, Char(13) + Char(10), 1) ,

       addr_address2 = dbo.fGetTokenN(addr_address, Char(13) + Char(10), 2) ,

       addr_address3 = dbo.fGetTokenN(addr_address,...

Works a treat apart from 1 thing.......it doesn't delete the char(13)'s while breaking up the field.  I had a look at the udf, but the code is going way over my head...

Thanks for that! I have a slight problem though.....There are some addresses that have a null value in the address field (in smaller towns, a company might be so big is...

fortunately I got some help from a friend, below seems to work well:

use crm

declare @company int

declare @ccount  int

declare cur cursor for

select comp_companyid from company

FOR UPDATE OF comp_esp

open cur

fetch next from...