|
|
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
|
|
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" <cforce[ at ]tampabaydotrrdotcom> wrote in message
news:050D0279-771B-4E90-8D4E-B73967395ECA[ at ]microsoft.com...
[Quoted Text] > 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
|
|
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" <schapel[ at ]mvps.org.ns> wrote in message
news:E23C1533-1ADA-4D16-9965-8B406701412B[ at ]microsoft.com...
[Quoted Text] > 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" <cforce[ at ]tampabaydotrrdotcom> wrote in message
> news:050D0279-771B-4E90-8D4E-B73967395ECA[ at ]microsoft.com...
>> 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
>
|
|
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.
--
Steve Schapel, Microsoft Access MVP
"Izod" <cforce[ at ]tampabaydotrrdotcom> wrote in message
news:FF4C4C09-F375-40B5-B50C-A03487750C18[ at ]microsoft.com...
[Quoted Text] > 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?
|
|
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" <cforce[ at ]tampabaydotrrdotcom> wrote in message
news:FF4C4C09-F375-40B5-B50C-A03487750C18[ at ]microsoft.com...
[Quoted Text] > 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" <schapel[ at ]mvps.org.ns> wrote in message
> news:E23C1533-1ADA-4D16-9965-8B406701412B[ at ]microsoft.com...
>> 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" <cforce[ at ]tampabaydotrrdotcom> wrote in message
>> news:050D0279-771B-4E90-8D4E-B73967395ECA[ at ]microsoft.com...
>>> 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
>>
>
|
|
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.
--
Steve Schapel, Microsoft Access MVP
"Izod" <cforce[ at ]tampabaydotrrdotcom> wrote in message
news:eosuHQ9XJHA.1532[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > 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.
|
|
Thank you very much Steve
Izod
"Steve Schapel" <schapel[ at ]mvps.org.ns> wrote in message
news:76072F11-2535-4571-AFC7-FC2C9656EC0A[ at ]microsoft.com...
[Quoted Text] > 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.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> "Izod" <cforce[ at ]tampabaydotrrdotcom> wrote in message
> news:eosuHQ9XJHA.1532[ at ]TK2MSFTNGP03.phx.gbl...
>> 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.
>
>
|
|
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
"Izod" <cforce[ at ]tampabaydotrrdotcom> wrote in message
news:57DF7E77-2381-4240-BDA0-0726FE964D1D[ at ]microsoft.com...
[Quoted Text] > Thank you very much Steve
> Izod
> "Steve Schapel" <schapel[ at ]mvps.org.ns> wrote in message
> news:76072F11-2535-4571-AFC7-FC2C9656EC0A[ at ]microsoft.com...
>> 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.
>>
>> --
>> Steve Schapel, Microsoft Access MVP
>>
>> "Izod" <cforce[ at ]tampabaydotrrdotcom> wrote in message
>> news:eosuHQ9XJHA.1532[ at ]TK2MSFTNGP03.phx.gbl...
>>> 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.
>>
>>
>
|
|
|