Use Left() function to update field

[Guest]

Hello,
I have a table that I pain-stakingly created from a word document (long
story, very long). Now that I have everything situated in the table I need
to break apart some fields.

Example:
before
ALLEY: STREET NAME:
Alley West of N Broadway St
Clinton St
Alley West of Hatfield St
Second Alley North of Tony Stein Way

after
ALLEY: STREET NAME:
Alley West of N Broadway St
Clinton St
Alley West of Hatfield St
Second Alley North of Tony Stein Way

I have created a table for look-up values containing all the pertinent alley
information, and as you can see there is no consistancy to the number of
characters I need to grab. I also have a table that has all the Street Names.

Is there a way to split this information using the Left() function or some
other function in SQL. I'd really hate to do this manually.

Thank you in Advance,

 

[Guest]

You could use a combination of InStr() and Left(), especially if 'of ' is a
part of any of these strings. The fact that you suggest using Left() in your
post indicates that these are relatively consistent (at least in the 'BEFORE'
format).

Try something like:

alleySpec = trim (left (whole, instr(1, whole, "of ") + 2))

The '+2' is needed because instr() points to the start of the target string.
This will work as long as you are starting from the beginning of the string.
To get rid of the string and be left with the street name, use replace
(whole, alleySpec, "", 1).

Once again, this requires some sort of constant pattern in the whole string
to identify. If there is none, I don't have any good suggestions for you.

Good luck!

 

[Guest]

Please forgive me, queries are not my specialty.
Field: | VAC_ALLEY | ST_NAME |
Table: | AVACLIST | AVACLIST |
Criteria: | ? | ? |

If the information resides in ST_NAME and I need to get it into VAC_ALLEY
where do I insert your line:
alleySpec = trim (left (whole, instr(1, whole, "of ") + 2))

 

[Guest]

OK, I got it to work but when there is no "of " it picks up the first one or
two letters.

 

[Guest]

This is what my query grid looks like

Field:VAC_ALLEY
Table:AVACLIST
Update To:Trim(Left([st_name],InStr(1,[St_name],"of ")+2))

 

[John Spencer (MVP)]

Try Adding where criteria, so that the update only applies to records with " of
" in them

WHERE: Like "* of *"

 

[Guest]

Thank you very much, you guys saved me many hours of mind numbing manual
editing.

Try Adding where criteria, so that the update only applies to records with " of
" in them

WHERE: Like "* of *"

This is what my query grid looks like

Field:VAC_ALLEY
Table:AVACLIST
Update To:Trim(Left([st_name],InStr(1,[St_name],"of ")+2))