querry to seperate data

[AlanO]

I was sent data that has address, city and state all in 1 field but I need to
break it out into the 3 separate fields. the data has carriage returns(used *
to represent return) after the address.

Est Number Est Site Address
051896-MA -002 25 ELM ST*SOUTH DEERFIELD, MA
011800-VT -001 4923 VT. RT 22A*ADDISON, VT

Any help that you could give would be greatly appreciated.

 

[John W. Vinson]

I was sent data that has address, city and state all in 1 field but I need to
break it out into the 3 separate fields. the data has carriage returns(used *
to represent return) after the address.

Est Number Est Site Address
051896-MA -002 25 ELM ST*SOUTH DEERFIELD, MA
011800-VT -001 4923 VT. RT 22A*ADDISON, VT

Any help that you could give would be greatly appreciated.

Which field is which here? Are there fields named [Est Number] - 051896
prehaps; [Est Site] - "MA -002" perhaps? and [Address]? Or is Site a field in
its own right?

And... how consistant is the data? Can you *reliably* count on the state to be
preceded by a comma and a blank and to consist of a two letter abbreviation,
or might you have some records with "BANGOR" and others with "STOWE, VERMONT"
and yet others with "IPSWITCH MA"?

You can use the Left() and Mid() functions with the help of the InStr
function: to find the position of a carriage return use

InStr([ADDRESS], Chr(13) & Chr(10))

 

[AlanO via AccessMonster.com]

I am interested in is the EST SITE ADDRESS field. The state is always the
last 2 characters preceded by a space and a comma. the issue that I am having
is that the address and the city are separated by a carriage return and are
never the same length. Is there some way to pull out data to the left of a
carriage return? and another to pull out data to the right of a carriage
return but only over as far as the comma? Here is a better sample of the data.
I used an * to represent the carriage return.

25 ELM ST*SOUTH DEERFIELD, MA
4923 VT. RT 22A*ADDISON, VT

Thank you for your help

I was sent data that has address, city and state all in 1 field but I need to
break it out into the 3 separate fields. the data has carriage returns(used *

[quoted text clipped - 5 lines]

Any help that you could give would be greatly appreciated.

Which field is which here? Are there fields named [Est Number] - 051896
prehaps; [Est Site] - "MA -002" perhaps? and [Address]? Or is Site a field in
its own right?

And... how consistant is the data? Can you *reliably* count on the state to be
preceded by a comma and a blank and to consist of a two letter abbreviation,
or might you have some records with "BANGOR" and others with "STOWE, VERMONT"
and yet others with "IPSWITCH MA"?

You can use the Left() and Mid() functions with the help of the InStr
function: to find the position of a carriage return use

InStr([ADDRESS], Chr(13) & Chr(10))

 

[KARL DEWEY]

Try these --
Est Number: Left([YourField], 14)

Based on what is the characters of your carriage return use this for street
address --
Mid([YourField], 16, 16 - InStr([YourField], Chr(13))-1, 99)
or
Mid([YourField], 16, 16 - InStr([YourField], Chr(10))-1, 99)
or
Mid([YourField], 16, 16 - InStr([YourField], Chr(13)& Chr(10))-2, 99)

Return of city and state --
Mid([YourField], InStr([YourField], Chr(13))+1, 99)
or
Mid([YourField], InStr([YourField], Chr(10))+1, 99)
or
Mid([YourField], InStr([YourField], Chr(13) & Chr(10))+2, 99)


--
Build a little, test a little.

I am interested in is the EST SITE ADDRESS field. The state is always the
last 2 characters preceded by a space and a comma. the issue that I am having
is that the address and the city are separated by a carriage return and are
never the same length. Is there some way to pull out data to the left of a
carriage return? and another to pull out data to the right of a carriage
return but only over as far as the comma? Here is a better sample of the data.
I used an * to represent the carriage return.

25 ELM ST*SOUTH DEERFIELD, MA
4923 VT. RT 22A*ADDISON, VT

Thank you for your help

I was sent data that has address, city and state all in 1 field but I need to
break it out into the 3 separate fields. the data has carriage returns(used *

[quoted text clipped - 5 lines]

Any help that you could give would be greatly appreciated.

Which field is which here? Are there fields named [Est Number] - 051896
prehaps; [Est Site] - "MA -002" perhaps? and [Address]? Or is Site a field in
its own right?

And... how consistant is the data? Can you *reliably* count on the state to be
preceded by a comma and a blank and to consist of a two letter abbreviation,
or might you have some records with "BANGOR" and others with "STOWE, VERMONT"
and yet others with "IPSWITCH MA"?

You can use the Left() and Mid() functions with the help of the InStr
function: to find the position of a carriage return use

InStr([ADDRESS], Chr(13) & Chr(10))

--



.

 

[John W. Vinson]

Mid([YourField], 16, 16 - InStr([YourField], Chr(13))-1, 99)

Some time back I discovered a neat fact: you can leave off the last argument
of Mid() - where you have the 99 - and it will return the rest of the string,
no matter how long it is. E.g.

Mid("1234567890", 2)

will return 234567890.

Thanks for getting the answer - I think I misunderstood the nature of the data
Alan was dealing with.

 

[AlanO via AccessMonster.com]

thanks I will try them out.

Try these --
Est Number: Left([YourField], 14)

Based on what is the characters of your carriage return use this for street
address --
Mid([YourField], 16, 16 - InStr([YourField], Chr(13))-1, 99)
or
Mid([YourField], 16, 16 - InStr([YourField], Chr(10))-1, 99)
or
Mid([YourField], 16, 16 - InStr([YourField], Chr(13)& Chr(10))-2, 99)

Return of city and state --
Mid([YourField], InStr([YourField], Chr(13))+1, 99)
or
Mid([YourField], InStr([YourField], Chr(10))+1, 99)
or
Mid([YourField], InStr([YourField], Chr(13) & Chr(10))+2, 99)

I am interested in is the EST SITE ADDRESS field. The state is always the
last 2 characters preceded by a space and a comma. the issue that I am having

[quoted text clipped - 28 lines]

InStr([ADDRESS], Chr(13) & Chr(10))