Proper case and postcodes 3

[oldmill]

I wish to format a name and address table in an access DB 2000 to "Title case". I can correct the most of the fields like name, Address line1 , Address line 2 and so on. When I get the Address line 4 and 5 I have the problem that there are some postcodes in these instead of the postcode field where they should be. When I run the update query on these fields it obiously does not handle them correctly. I am using this update query StrConv([Addressline1],3). Can anyone help

 

[GingerR]

You say it does not handle them correctly. How is it not handling them correctly? Please post sample data, how it is wrong, and how you want it to be.

Also...do you run anything to put the post code (i assume you mean zip code?) into the correct field?

Thanks--g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Blorf]

To add to GingerR's suggestion, you wrote that the post code is located in the wrong field. Are you able to identify these records? If so, maybe we can help to correct that instead, as I suspect that's the real issue.

Thanks,
ChaZ

 

[oldmill]

Thanks for your reply. Postcodes are the UK equivelent of ZIP codes but they differ in their format BT389JN, XF432Y or GD1 3RK. I believe that I am right in Saying that they always have 2 letters and then a numeric number as standard. I have had another look a my table and I think it may not be a massive job to move these manually but it would be good to know how to identify them in the future and then move them to a different field with a query.
I have also came accross a different problem in relation to proper case. I have a lot of McGookin's, McSerry's and many more McSomething's how can I handle these in my update query to correct Tiltle Case?

 

[KingMob]

Oldmill, just noticed your post. I've had to do a fair bit of db work with postcodes so hope this helps.

There are a lot of postcodes where there is only a single letter g - Glasgow (e.g. G1 2DN), E for East London, M for Manchester and so on. There are also a few London postcodes I have come across where there is a letter after the first set of numbers e.g. WC12A 2DD. The only 'rules' I have found you can apply is that the code always begins with a letter and that after the space there is always 3 characters.