remove text

[Guest]

i have an address field called addr that list the street address, city,
state, zip
ex
33 Oliver St, Reading, MA 06103
i want to get rid of the comma after the street address the city, state zip

 

[tina]

recommend you normalize your data, by putting each "piece" of data into its'
own field, as

StreetAddress
City
State
Zip

then you can concatenate the fields in any format you choose for display
purposes.
to learn more about normalization, see
http://home.att.net/~california.db/tips.html#aTip1

hth

 

[Smartin]

i have an address field called addr that list the street address, city,
state, zip
ex
33 Oliver St, Reading, MA 06103
i want to get rid of the comma after the street address the city, state zip

Create a VBA function in a standard module thus (untested):

Public Function RemoveCommas (FromString as String) As String
Dim L as Long
Dim i as Long
Dim c as String
Dim tmps as String
L = Len(FromString)
i = 1
Do While i <= L
c = Mid (FromString, i, 1)
If c <> "," Then
tmps = tmps & c
EndIf
i = i + 1
Loop
RemoveCommas = tmps
End Function


Then call the function in your query thus:

SELECT RemoveCommas (Nz (addr, ""))
FROM MyTable;


HTH

 

[Dirk Goldgar]

Create a VBA function in a standard module thus (untested):

Public Function RemoveCommas (FromString as String) As String
Dim L as Long
Dim i as Long
Dim c as String
Dim tmps as String
L = Len(FromString)
i = 1
Do While i <= L
c = Mid (FromString, i, 1)
If c <> "," Then
tmps = tmps & c
EndIf
i = i + 1
Loop
RemoveCommas = tmps
End Function


Then call the function in your query thus:

SELECT RemoveCommas (Nz (addr, ""))
FROM MyTable;

Why not just use the VBA Replace function?

 

[Guest]

I'm sorry I just realized I was not clear in my question.

I want to get rid of everything after the street addess including the comma

 

[Dirk Goldgar]

I'm sorry I just realized I was not clear in my question.

I want to get rid of everything after the street addess including the
comma

An expression like this will return everything up to but not including
the first comma:

Left([Addr], InStr([Addr], ",") - 1)

You could use that in an update query or in VBA code, depending on what
you're trying to do.

 

[Guest]

I am not that familiar with access or the VBA function. I am more familiar
with the query function. If you want to provide some instructions on the VBA
I would be glad to try it.

 

[Dirk Goldgar]

I am not that familiar with access or the VBA function. I am more
familiar with the query function. If you want to provide some
instructions on the VBA I would be glad to try it.

Now that I know more about what you want to do, I see that the Replace
function isn't really appropriate. See my other reply in this thread
for an expression you can use in an update query to truncate the [addr]
field before the first comma.

 

[Smartin]

Why not just use the VBA Replace function?

Heh, I forget that's around. Was that in A97?

 

[Guest]

Hi Smartin,

No. The Replace function was introduced in Access 2000.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

This worked great for one listing but when I run it as update query for a lot
of listings I get validation and violation error messages. I looked ot the
data and some of the entries in this addr field have only partial address
info for ex.

Weston, MA 05118 as opposes to the full address

1054 Falmoth Ave, Weston, MA 05118

Could that be what is causing the validation error because the data is
different and not all entries have the same amount of info to remove. Again
all I want left is the street address. Thanks

I'm sorry I just realized I was not clear in my question.

I want to get rid of everything after the street addess including the
comma

An expression like this will return everything up to but not including
the first comma:

Left([Addr], InStr([Addr], ",") - 1)

You could use that in an update query or in VBA code, depending on what
you're trying to do.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

This worked great for one listing but when I run it as update query
for a lot of listings I get validation and violation error messages.
I looked ot the data and some of the entries in this addr field have
only partial address info for ex.

Weston, MA 05118 as opposes to the full address

1054 Falmoth Ave, Weston, MA 05118

Could that be what is causing the validation error because the data is
different and not all entries have the same amount of info to remove.
Again all I want left is the street address. Thanks

Fields with partial addresses shouldn't raise an error, so long as
there's at least one comma. But fields that are blank, or contain no
comma at all, will raise an error with that code.

You could, of course, use criteria in your update query to filter out
such records, but your example of an address containing only city,
state, and zip demonstrates a common problem with logic of this sort.
How much variation is there in the data? You could conceivably apply
two update queries with different criteria, one for when there appears
to be a full address, and another for when there isn't, like these:


UPDATE YourTable
SET [Addr] = Null
WHERE Not [Addr] Like '*,*,*'

UPDATE YourTable
SET [Addr] = Left([Addr], InStr([Addr], ",") - 1)
WHERE [Addr] Like '*,*,*'

Note that the above queries would have to be run in that order, because
each modifies the values previously in the field.

I'm assuming that you've already extracted your city, state, and zip
into separate fields, because you won't be able to extract them after
these update queries have run.

 

[Guest]

Thanks for the info, but I am a novice at access when it come to running
queries so please be patient. By the way this forum is great for people like
me learning this. How would I go about using the info you provided. When I
opened a query in design view and went to update query and inserted the first
instructions into the correct field is gove me an error of invalid syntax.
Can you give me a step by step to get me going. Much appreciated.

This worked great for one listing but when I run it as update query
for a lot of listings I get validation and violation error messages.
I looked ot the data and some of the entries in this addr field have
only partial address info for ex.

Weston, MA 05118 as opposes to the full address

1054 Falmoth Ave, Weston, MA 05118

Could that be what is causing the validation error because the data is
different and not all entries have the same amount of info to remove.
Again all I want left is the street address. Thanks

Fields with partial addresses shouldn't raise an error, so long as
there's at least one comma. But fields that are blank, or contain no
comma at all, will raise an error with that code.

You could, of course, use criteria in your update query to filter out
such records, but your example of an address containing only city,
state, and zip demonstrates a common problem with logic of this sort.
How much variation is there in the data? You could conceivably apply
two update queries with different criteria, one for when there appears
to be a full address, and another for when there isn't, like these:


UPDATE YourTable
SET [Addr] = Null
WHERE Not [Addr] Like '*,*,*'

UPDATE YourTable
SET [Addr] = Left([Addr], InStr([Addr], ",") - 1)
WHERE [Addr] Like '*,*,*'

Note that the above queries would have to be run in that order, because
each modifies the values previously in the field.

I'm assuming that you've already extracted your city, state, and zip
into separate fields, because you won't be able to extract them after
these update queries have run.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for the info, but I am a novice at access when it come to
running queries so please be patient. By the way this forum is great
for people like me learning this. How would I go about using the
info you provided. When I opened a query in design view and went to
update query and inserted the first instructions into the correct
field is gove me an error of invalid syntax. Can you give me a step
by step to get me going. Much appreciated.

What I gave you was the full SQL statement for each query, so you can't
just paste it into a field in the query designer. Instead, open a new
query in design view, close the "Add Table" dialog without adding any
table, and then switch to SQL View. You'll see a SQL statement
consisting solely of this:

SELECT;

In place of that, paste the SQL statement I gave you, and replace
"YourTable" with the name of the table you want to update; also make
sure that the field name "Addr" is correct. Having done that, you can
either go ahead and run the query (by clicking the "!" toolbar button,
or menu item Query -> Run), or you can switch back to design view to see
what the query would look like when presented in that form.

 

[Guest]

hi Dirk, I very much appreciate your responses. When I rin this update I get
a message that I do not have enough disk space or memory to undo the changes
this action query is about to make. I should have plenty of space. Any
suggestions?

 

[Guest]

Hi Finger Tips,

Please see this KB article for a resolution:

You may receive a "There isn't enough disk space or memory" error
message when you perform an operation on an Access table
http://support.microsoft.com/kb/286153


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Still getting the violation and validation error message, even when running
it the way you describe here.

This worked great for one listing but when I run it as update query
for a lot of listings I get validation and violation error messages.
I looked ot the data and some of the entries in this addr field have
only partial address info for ex.

Weston, MA 05118 as opposes to the full address

1054 Falmoth Ave, Weston, MA 05118

Could that be what is causing the validation error because the data is
different and not all entries have the same amount of info to remove.
Again all I want left is the street address. Thanks

Fields with partial addresses shouldn't raise an error, so long as
there's at least one comma. But fields that are blank, or contain no
comma at all, will raise an error with that code.

You could, of course, use criteria in your update query to filter out
such records, but your example of an address containing only city,
state, and zip demonstrates a common problem with logic of this sort.
How much variation is there in the data? You could conceivably apply
two update queries with different criteria, one for when there appears
to be a full address, and another for when there isn't, like these:


UPDATE YourTable
SET [Addr] = Null
WHERE Not [Addr] Like '*,*,*'

UPDATE YourTable
SET [Addr] = Left([Addr], InStr([Addr], ",") - 1)
WHERE [Addr] Like '*,*,*'

Note that the above queries would have to be run in that order, because
each modifies the values previously in the field.

I'm assuming that you've already extracted your city, state, and zip
into separate fields, because you won't be able to extract them after
these update queries have run.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Still getting the violation and validation error message, even when
running it the way you describe here.

Can you tell exactly what validation rule is being violated?

Did you resolve the other problem you posted about, the "not have enough
disk space or memory" error?

 

[Guest]

I was able to use the fix from Tom to get rid of the disk space message. I
also noticed that some of the addr entries do not have any text in them which
may be causing the violation and validation message. So there is actually 3
different scenerios.


1054 Falmoth Ave, Weston, MA 05118

Weston, MA 05118

Or just blank

 

[Dirk Goldgar]

I was able to use the fix from Tom to get rid of the disk space
message. I also noticed that some of the addr entries do not have
any text in them which may be causing the violation and validation
message.

But you still haven't reported the exact message. That might help
figure out exactly what's wrong, as I thought the queries I posted
before wouldn't have raised an error.