Stripping out specific characters 2

[mickeyg]

I have two tables in a query linked by a field that contains character data such as 14 1/2.

One of the tables is an imported table where the user might have data such as 14 1/2".

How do I strip out the single and double quotation marks in my criteria?

Thanks,
Mickey

 

[tlbroadbent]

Do you want to remove the single and double quotes from the data in the table or only remove it when displayed? Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[MichaelRed]

Toughie.

I can only think to do this with a "walk" through the string.

This is my (query SQL.
SELECT tblInches.Inches, basRemoveQuote([Inches]) AS INW_O_Quo
FROM tblInches;


This is the Results. The first col is the source, the second is the result.

Inches INW_O_Quo
"4 1/2""" 4 1/2
"37.6""" 37.6
4 4
121 121
"7 1/4""" 7 1/4
"8' 4 3/4 """ 8' 4 3/4


This is the function:

Public Function basRemoveQuote(StringIn As String) As String

    Dim Idx As Integer
    Dim strChr As String
    Dim strReplace As String

    For Idx = 1 To Len(StringIn)
        strChr = Mid(StringIn, Idx, 1)
        If (strChr <> Chr(34)) Then
            strReplace = strReplace & strChr
        End If
    Next Idx

    basRemoveQuote = strReplace


End Function

hopefullly you can see how to use this in an UPDATE query.


MichaelRed
redmsp@erols.com

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

 

[mickeyg]

I am looking to remove anything that is not a number or the slash mark from the data.

Basically this is an update query where my criteria is tblImport.Weight = tblConv.Length. Then I update tblImport to the matching tblConv.Weight field.

Maybe I need to run a data massaging query before I run this update query.

Mickey

 

[MichaelRed]

Public Function basRemoveQuote(StringIn As String) As String

Dim Idx As Integer
Dim strChr As String
Dim strReplace As String

For Idx = 1 To Len(StringIn)
strChr = Mid(StringIn, Idx, 1)
If (IsNumeric(strChr) Or MyChr = "/&quot Then
strReplace = strReplace & strChr
End If
Next Idx

basRemoveQuote = strReplace


End Function



MichaelRed
redmsp@erols.com

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

 

[mickeyg]

Thanks Michael.

I will give that a shot.

Mickey

 

[Quehay]

Would the Val function work in this context? If all of the values are ostensibly numeric it might be able to reduce them to the Single/Double value that the string represents.

 

[MichaelRed]

No. Val will stop at the first non-numeric char.

MichaelRed
redmsp@erols.com

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

 

[Quehay]

Oh yeah that's right!