Identify the position number of a text character. 4

[texan1956]

I need to be able to determine the position number of a character within a text field. Is there a function that I can use to identify this? (ex: Mid()??) I have an update query created with the criteria set for finding records with two or more consecutive spaces embedded in the field. What I'm looking to do is to identify the position where these spaces begin and then use the Left and Right functions to concatenate the text away from the extra spaces.

 

[RickSpr]

You need the InStr() function. Rick Sprague

 

[raskew]

To see how the InStr() function could work, you might try this query, using a COPY of Northwind's (table) Product (Products1).

The idea here is to find any ProductName containing "ee" and display the "ee" as "~~".

SELECT Left([ProductName],InStr([productname],"ee&quot-1) & "~~" & Mid([ProductName],InStr([Productname],"ee&quot+2) AS Expr1
FROM Products1
WHERE (((InStr([productname],"ee&quot)>0));

If you wanted to eliminate any instances of "ee" entirely, the same code minus the & "~~" would do it.

To actually make the changes shown, you could use an update query based on ProductName where the criteria is InStr([ProductName], "ee&quot > 0 and the Update To is

Left([ProductName],InStr([productname],"ee&quot-1) & "~~" & Mid([ProductName],InStr([Productname],"ee&quot+2)

Just remember to use a copy of your table, not the original.

 

[MichaelRed]

? Replace("The Quick Brown Dog Jumped over the Lazy Red Fox", " ", " &quot
The Quick Brown Dog Jumped over the Lazy Red Fox

Any reason the Replace method won't do it? (not that the above example is replacing the two spaces with a single one.)

If this is not correct (and I suspect it is NOT), then the following should be pretty close:

Public Function basSingleSpace(strIn As String) As String
    'To Remove all but one space between words in a string
    'Note that "SPACE" here refers to ANY non-printing ASCII character.

    Dim Idx As Integer
    Dim MyChr As Integer
    Dim tnpStr As String
    Dim spcCount As Integer

    For Idx = 1 To Len(strIn)

        MyChr = Asc(Mid(strIn, Idx, 1))

        If (MyChr > 32 And MyChr < 128) Then
            tmpStr = tmpStr & Mid(strIn, Idx, 1)
            spcCount = 0

         Else

            If (spcCount = 0) Then
                tmpStr = tmpStr & &quot; &quot;
                spcCount = spcCount + 1
            End If

        End If

    Next Idx

    basSingleSpace = tmpStr

End Function

Note that this considers ALL non-printing characters as "Spaces", so you may need to think about wheather this is REALLY suitable. If not, you have the code, so you could modify it to suit the specifics.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[raskew]

Michael-

I'll show my ignorance here--where is the Replace() function coming from? Is it user-defined? Although easily constructed, can't find a reference to an Access function.

Bob

 

[RickSpr]

Replace is a VBA6 function available in Access 2000. Rick Sprague