Generate ID using parts of other records

[ali32j]

Hi All

I m looking to use an update query to generate a ID against each customer, i want to use the following

Region Name PostCode
EPEG ROCALLER ELECTRONICS LTD FG40 4RF

I want the ID generated to be:

EPEGFG404ROCALLELECTRO

So it would take all of the region record, 6 letters of first and second words in Name record and 5 digits of the postcode ignring the space...

I know how to do this in excel, but at a loss with a query, and what code i would need

Can anyone help??

Ali

 

[Remou]

As in:

[tt]Region & Left(Replace([PostCode]," ",""),5) & Left(Replace([Name]," ",""),6)[/tt]

http://lessthandot.com

 

[ali32j]

Hi Remou

Please accept apologies for delay, i should have given more details and the above is quite right, i need the following:

1. region

2. All of the first part of post code regardless of length (which varies, it could be 3 digits or 4, PO3 or PO34) + first letter after the space in postcode

3. All of the first part of company name regardless of length and only 6 digits of the second part of company name (but needs to ignore it if second part is LTD)

SOrry i did not develop such a complex ID but short changing the a whole companies ID structure i have to abide by it

Would appreciate any help you could offer

Thanks

Ali