VBA - how to isolate several numerics within a field (data type: text)?

[frade]

Hello, ;-)

I'm working now with MS Access
In my table, I have several fields and one field (text type) called ' contact'
with the following informations:

first name name,name_of_the street,number_of_street,zip_code,city

I have thus several informations separated by commas

Something like this:

Jeff Dupont,street of peace,17,59000,Lille

I would like to create a new field with only the zip code (59000)
I said by myself that it would be necessary to recover the 5
following caracters immediately after the 3rd comma but I don' know how to isolate
this position

What can I do with a macro or a procedure VBA?

Thanks a lot!

Fran?ois

[TonyJollans]

Hi Fran?ois,

Try an Update Query with some SQL like this:

UPDATE YourTable
SET YourTable.ZipCode = Mid([Contact],InStr(InStr(InStr([Contact],",")+1,[Contact],",")+1,[Contact],",")+1,5);

You will get errors if there are any Contacts without three commas but you can ignore them.

There shouldn't be any need for VBA.