Simple query to remove Information from Column

[trevor2524]

Hello everybody. I have a query that has pulled information from a mseter table. On the query in one columns i have information that is scattered and mixed together. Does anybody know a query i can run against a column with the column header as "Appends" that will remove everything that doesn't include the word "phones". The information shows up on my column as "Phonesand a number goes here)" I would need to keep everything including the number but remove everything else found around phones.

 

[CJ_London]

something like

UPDATE mastertable SET phonenum= Replace([phonenum],"Phones","");

 

[trevor2524]

something like

UPDATE mastertable SET phonenum= Replace([phonenum],"Phones","");

Almost that is actually doing the opposite:. I have the following code placed:
UPDATE [Testing Log] SET Appends=Replace([Appends],"Phones","");

Also the way the data shows up is the following: Phones:23 or Phones:25. I would need all that information to stay. So after the query runs all the column should say in the Phones:with the number.

 

[CJ_London]

perhaps provide some example data as it is now and what it should look like afterwards

 

[trevor2524]

perhaps provide some example data as it is now and what it should look like afterwards

The column has information that looks like this:

Phones:25~testinginfo:3~Testinfo:7
testingof:9~Phones:38~Testinginfo:9

The results should look like this:
Phones:25
Phones:38

 

[arnelgp]

if you ever need an overkill function, then put this in a module.
untested.

public function fnPhonesOnly(v as variant) as variant
dim ret as variant
dim l as long
dim i as long
dim newText As String

' check if the word "phones:" is in the text
if Instr(v & "", "Phones:") <> 0 Then
ret = Split(v & "", "Phones:")
NewText = "Phones:"
For l = LBound(ret) to UBound(ret)
i = 1
while IsNumeric(Mid(ret(i), i, 1))
NewText = NewText & Mid(ret(i), i, 1)
i = i + 1
wend
ret(i)=NewText
Next
fnPhonesOnly=Join(ret, " ")
else
fnPhonesOnly = v
end if
end function

to call it:

UPDATE [Testing Log] SET Appends=fnPhonesOnly([Appends]);

 

[trevor2524]

Thanks for the respsonse. THe function is getting an error message of subscript out of range at this line of code:

"while IsNumeric(Mid(ret(i), i, 1))"

 

[arnelgp]

i am very sorry, please modify this portion:

while IsNumeric(Mid(ret(i), i, 1))
NewText = NewText & Mid(ret(i), i, 1)
i = i + 1
wend
ret(i)=NewText

with this:

while IsNumeric(Mid(ret(l), i, 1))
NewText = NewText & Mid(ret(l), i, 1)
i = i + 1
wend
ret(l)=NewText


:: l there is small letter L.

 

[CJ_London]

for the examples given


"Phones:" & val(mid([myfield],instr([myfield],"Phones:")+7))

 

[trevor2524]

is that in conjunction with your orginal sql statement. if you can can you provide the entire sql statement.

 

[arnelgp]

tested version:

Public Function fnPhonesOnly(v As Variant) As Variant
Dim ret As Variant
Dim l As Long
Dim i As Long
Dim newText As String
Dim bolTrue As Boolean
' check if the word "phones:" is in the text
If InStr(v & "", "Phones:") <> 0 Then
    ret = Split(v & "", "Phones:")
    newText = "Phones:"
    For l = LBound(ret) To UBound(ret)
        i = 1
        bolTrue = False
        While IsNumeric(Mid(ret(l), i, 1))
            If Not bolTrue Then bolTrue = True
            newText = newText & Mid(ret(l), i, 1)
            i = i + 1
        Wend
        If bolTrue Then
            ret(l) = newText
        Else
            ret(l) = ""
        End If
    Next
    fnPhonesOnly = LTrim(Join(ret, " "))
Else
    fnPhonesOnly = v
End If
End Function

 

[trevor2524]

That works. Thank you!

 

[CJ_London]

UPDATE mastertable SET phonenum="Phones:" & val(mid([phonenum],instr([phonenum],"Phones:")+7));