Please Make Query For Me………….

[Chetan Kumar]

Hi All,

Please Suggest me how can I split long string throw query please give some examples
I have records something like that:
K Mirza (905) 820-6791 3759 Nightstar Dr Mississauga ON L5M 8A5
K Mistry Shailesh (905) 501-0311 518 Galaxy Crt Mississauga ON L5R 2R2
K MOHAMED (905) 896-7280 1190 Forestwood Dr Mississauga ON L5C 1H9

All data in access table.
I want Name, Mobile No, Address , City, Zip Code in same table next fields
Please Help.

 

[Chetan Kumar]

Any Idea Plzzzzzzzzzzzzzzzzz??????:banghead:

 

[Simon_MT]

The slight difficulty is that the 2nd Mobile is one character less than the others. I have called your text CText: so:

PosMobile: InStr(1,[CText],"(")

will find the first "(" of the Mobile No:

therefore the PosMobile-1 will give the lenght of the Name:

TmpName: Left([CText],[PosMobile]-1)

Whilst the postcode can be derived

TmpPostcode: Right([CText],6)

That should get you started.

Simon

 

[Brianwarnock]

That's the easy bit, and assumes all postcodes are the same length , ours aren't , are india's?

The problem with parsing data is inconsistency and it is very unlikely in names and addresses.

Questions

1 Do all addresses have zip codes and
2 are they all the same length.
3 do they all have a. Mobile phone number

If the answer to all of these is yes you stand a chance, if all mobile phone numbers were the same length it would be easy but you are going to have to write code looking for numerics, if they are 18 or 19 I suppose you could always pull 19 and trim right.

What is the ON is that part of the zip code or the city name?

Brian

 

[spikepl]

You have already obtained an answer to how to parse your data here: http://www.access-programmers.co.uk/forums/showthread.php?t=244044 so what is the point of asking this again?

 

[Brianwarnock]

Thank you for that spike, I'm only glad that I was to busy yesterday to waste my time writing code.

Having read the other thread I wonder if Chetan is asking how to use the function, he has not asked that but his last post on that thread kind of implied it , I think. He certainly should have cross referenced that thread here.

Brian

 

[Chetan Kumar]

Hi all,

thanks i have already answer but i want to know all the way in which we can handle that kind of data.......so throw macro its going awesome its really working beautiful ......................so thanks for every thing