Re: Find And Replace Text String VBA

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 09/22/04 

Date: Tue, 21 Sep 2004 23:04:44 -0400

Public Function RemoveDoubleSpaces(ByVal strOriginalValue) As String
Do While InStr(strOriginalValue, " ") > 0
    strOriginalValue = Replace(strOriginalValue, " ", " ", 1, -1,
vbTextCompare)
Loop
RemoveDoubleSpaces = strOriginalValue
End Function 

-- 
        Ken Snell
<MS ACCESS MVP>
"wadev1" <wadev1@discussions.microsoft.com> wrote in message
news:A2820E1D-9891-468C-BC2B-3036F07BFA9C@microsoft.com...
> Also, I am running Access 2000.
>
> "wadev1" wrote:
>
> > Does anyone have any code on how to replace specified text string within
a
> > text string?  I am trying to eliminate all spaces within a Address field
, ex
> > 123 SAINT JOSEPH CT.  I have a update query that I created that will
give me
> > the desired result, but I have to run it multiple times to replace all
the
> > spaces.  I am using a InStr function to locate the Start position and
then I
> > concatenate the left and Mid portions (minus the space) to get the
desired
> > results.  I have done a LTrim if the location =1 and a criteria <>0.
How
> > could I run the query multiple times, please give an example of some
code?
> >
> > The update query, "UPDATE ADDRESS QUERY" has a select query linked to
the
> > actual "ADDRESS" table.  The select query, "ADDRESS TEXT LOCATION" has a
> > field "LOCATION' with a location value of the start of the text.  If the
loop
> > could stop with the Sum of [ADDRESS TEXT LOCATION]![LOCATION] =0, that
would
> > be great.  Thanks in advance.

Relevant Pages

  • Re: Text To Columns in Access
    ... ' Purpose: Return section of string ... Access MVP 2002-2005, 2007 ... Doug Steele, Microsoft Access MVP ... I tried entering this command into a new query column as CR1: ...
    (microsoft.public.access.queries)
  • Re: How do you dump the contents of a query into an Excel speadshe
    ... Runtime error '3075' syntax error in string in query expression ... "Ken Snell " wrote: ... <MS ACCESS MVP> ... I borrowed this code from your "Create a Query and Export multiple ...
    (microsoft.public.access.queries)
  • Re: dynamic query using parameter function
    ... Function is a vba function which return the ... You can't really do that, but you can fake it, if you don't mind that the query will be much slower. ... Then change your query so that it uses the InStr function to determine whether x is in that string: ... Dirk Goldgar, MS Access MVP ...
    (microsoft.public.access.modulesdaovba)
  • Re: How to create this query
    ... string "serial#" just before the actual serial number? ... Ken Snell ... <MS ACCESS MVP> ... in the same or another query search for lines in table ...
    (microsoft.public.access.queries)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)