correcting data fields

I

Izod

I have in the past (longtime ago) corrected data in a field using a macro or
query. For example; I got to the 5th position in the field, then copied
the next four positions, then placed them in a new field. A good example
is; getting the house number only from an address that had house number and
street name.

5555 Washington St cut the 5555 and put it into a new field set up for str
number.

Can someone tell me where to start looking, I have a dozen access books but
can't seem to find the answer.
Thanks for any help

Izod
 
S

Steve Schapel

Izod,

It would normally not be a valid database design to have the street number
in a separate field.

Nevertheless, if *all* the addresses in the table have the same strucure,
i.e. number followed by space followed by street, you could use an Update
Query to write the house number to the new field, using an exporession such
as:
Left([YourAddressField],InStr([YourAddressField]," ")-1)
 
I

Izod

Ok, Steve I now read your response a little better then before. The
numbers in the street name field
keeps me from sorting by street name. Unless I can do something in the sort
to by pass the 1st 5 characters, how else would I get it in st name
sequence?

Chet
Steve Schapel said:
Izod,

It would normally not be a valid database design to have the street number
in a separate field.

Nevertheless, if *all* the addresses in the table have the same strucure,
i.e. number followed by space followed by street, you could use an Update
Query to write the house number to the new field, using an exporession
such as:
Left([YourAddressField],InStr([YourAddressField]," ")-1)

--
Steve Schapel, Microsoft Access MVP

Izod said:
I have in the past (longtime ago) corrected data in a field using a macro
or query. For example; I got to the 5th position in the field, then
copied the next four positions, then placed them in a new field. A good
example is; getting the house number only from an address that had house
number and street name.

5555 Washington St cut the 5555 and put it into a new field set up for
str number.

Can someone tell me where to start looking, I have a dozen access books
but can't seem to find the answer.
Thanks for any help

Izod
 
S

Steve Schapel

Izod,

Ok, fair enough. I hadn't thought of the option that you might want to sort
by street name, which I have never encountered a requirement for this. So
in this case, I can see some merit to the idea. I still wouldn't do it like
that myself. I would leave the address intact, and make a calculated field
in a query to use for sorting, something like this:
SortByStreet: Mid([Address],InStr([Address]," ")+1)

Again, this relies on all records following the same pattern, with street
name always following the first space in the address... otherwise you need
to take that into account for the exceptions.
 
I

Izod

Steve, you must think I'm screwy... Sorry I sent the response to you, it
came back. I didn't intend to send it to you anyway. Thanks for the
response. Are you aware of any books of standard code for the most common
functions. I appreciate your help. Thanks again.

Izod
Izod said:
Ok, Steve I now read your response a little better then before. The
numbers in the street name field
keeps me from sorting by street name. Unless I can do something in the
sort to by pass the 1st 5 characters, how else would I get it in st name
sequence?

Chet
Steve Schapel said:
Izod,

It would normally not be a valid database design to have the street
number in a separate field.

Nevertheless, if *all* the addresses in the table have the same strucure,
i.e. number followed by space followed by street, you could use an Update
Query to write the house number to the new field, using an exporession
such as:
Left([YourAddressField],InStr([YourAddressField]," ")-1)

--
Steve Schapel, Microsoft Access MVP

Izod said:
I have in the past (longtime ago) corrected data in a field using a
macro or query. For example; I got to the 5th position in the field,
then copied the next four positions, then placed them in a new field. A
good example is; getting the house number only from an address that had
house number and street name.

5555 Washington St cut the 5555 and put it into a new field set up for
str number.

Can someone tell me where to start looking, I have a dozen access books
but can't seem to find the answer.
Thanks for any help

Izod
 
S

Steve Schapel

Izod,

There are some Access books around that have good sections on the bult-in
functions. One that I am familiar with is:
Building Access Applications, by John Viescas.
 
I

Izod

Thank you very much Steve
Izod
Steve Schapel said:
Izod,

There are some Access books around that have good sections on the bult-in
functions. One that I am familiar with is:
Building Access Applications, by John Viescas.
 
I

Izod

Steve, I ordered that book, meanwhile going through others that I have, I
run across "Access Hack" by Ken Blutman. Publisher O'Reilly. A bunch of
tools..... Thanks again

Izod
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top