query to look within field for a number

[wolfm]

I have a situation where one of my table fields is set as text, even though I am using that field for numbers. Reason being is that sometimes the excel database I am importing from will contain text in that field.
Now, I am wanting to have my reports total the numbers in that field. This is a new feature I decided to add later. When I try to change the field to text, it tells me it will delete much of my data. I don't want to do that.
Instead of re-designing the orginal excel database and import steps, is there a way to modify my query so that it searches that particular field for numbers only? (dis-regard text)
I'm thinking perhaps not, and I would have to add an additional field for numeric only. But, if anyone has any ideas, please advise.

Thank you!

[Bob Fitz]

Will the number of text characters in each field that has them vary.
Will the position of text characters in each field vary.
Perhaps a few examples would help.

[wolfm]

Yes, number of characters can vary, although never seems to exceed 15 characters or so.
Position of characters also can vary. Basically, the user entering the original data in Excel can enter anything they want, the way it is set up now. But I can edit before importing the records into Access, if that would help the query. Just trying to limit the amount of editing that I have to do. Thank you!

[orange]

Here is a function derived from a Dev Ashish post. It will take in a string and return only the numeric characters.

'---------------------------------------------------------------------------------------
' Procedure : fExtractStrNums
' Author    : user
' Date      : 2/27/2009
' Purpose   :   To extract only numerics from a string of mixed alpha,
'numerics and special chars.
'---------------------------------------------------------------------------------------
'
Function fExtractStrNums(ByVal strInString As String) As String
' From Dev Ashish
'(Q) How do I extract only characters from a string which has both numeric and alphanumeric characters?

'(A) Use the following function. Note that the If loop can be modified to extract either both Lower and Upper case character or either
'Lower or Upper case characters.

'************ Code Start **********

Dim lngLen As Long, strOut As String
Dim i As Long, strTmp As String

   On Error GoTo fExtractStrNums_Error

    lngLen = Len(strInString)
    strOut = ""
    For i = 1 To lngLen
        strTmp = Left$(strInString, 1)
        strInString = Right$(strInString, lngLen - i)
        
   'ADJUSTED CODE TO GET ONLY NUMERICS
   
        If (Asc(strTmp) >= 48 And Asc(strTmp) <= 57) Then
            strOut = strOut & strTmp
        End If
    Next i
    fExtractStrNums = strOut
      On Error GoTo 0
   Exit Function

fExtractStrNums_Error:
    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure fExtractStrNums of Module Module1"
End Function

Good luck

[wolfm]

In design view of my query, I right clicked in criteria under that field, build event, and copied the code into the box. Is this correct? Not really sure how to enter code.
When I do this, returns the error "The expression you entered contains invalid syntax, or you need to enclose your data in quotes". Thank you.

[June7]

This code needs to be in a general VBA module. Then the query will call the function.

Open the VBA editor from Database Tools on the ribbon. Create a module (Insert > Module). Copy/paste the posted code.

In the query, call the function in an expression that will create a field. Do this on the Fields row.

JustNums: fExtractStrNums([fieldname])

Be aware if the field has no data, the function will error.

[Bob Fitz]

To use the function that orange offered it would need to be pasted into a General module, then called from within a querry. See example db attached:

[wolfm]

Thanks All! It works great! You are awesome!!