query to return part of a field (1 Viewer)

mounty76 · Sep 17, 2017

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 · Sep 17, 2017

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 · Sep 17, 2017

create a Public Function in a module:

Code:

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 · Sep 18, 2017

arnelgp said:
create a Public Function in a module:

Code:

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!

query to return part of a field (1 Viewer)

mounty76

Registered User.

Gasman

Enthusiastic Amateur

arnelgp

..forever waiting... waiting for jellybean!

jumbotrano

Registered User.

Similar threads

Users who are viewing this thread