query to return part of a field

[mounty76]

Hi, is there any way to enter criteria in to a query to return only the numbers in a string? I have imported a xlsx into a table and I have a column that is called White List, in this column are records such as Yes 123456, I want to make a query that only returns the numbers then make it an update query....otherwise I got 1000 records to type out cheers

 

[Gasman]

If they are all in that format, you could look for the space and then take everything to the right?

Something along the lines of

mid(tt,instr(1,tt, " ")+1,len(tt)-instr(1,tt," "))

where tt is your field

 

[arnelgp]

create a Public Function in a module:

Public Function NumOnly(fld As Variant) As Long
	dim s As String
	Dim ln As Long
	fld = Trim(fld & "")
	If fld = "" Then Exit Function
	ln =Len(fld)
	s = Mid(fld,ln,1)
	While IsNumeric(s)
		ln = ln -1
		s=Mid(fld,ln,1)
	Wend
	NumOnly = CLng(Mid(fld, ln+1)
End Function

Now call this function in your query:

SElect id, name, NumOnly(fieldName) From table1

 

[jumbotrano]

create a Public Function in a module:

Public Function NumOnly(fld As Variant) As Long
    dim s As String
    Dim ln As Long
    fld = Trim(fld & "")
    If fld = "" Then Exit Function
    ln =Len(fld)
    s = Mid(fld,ln,1)
    While IsNumeric(s)
        ln = ln -1
        s=Mid(fld,ln,1)
    Wend
    NumOnly = CLng(Mid(fld, ln+1)
End Function

Now call this function in your query:

SElect id, name, NumOnly(fieldName) From table1

Good explanation, thanks!