COURSE of the MONTH

Data Type Mismatch in Criteria Expression in MS Access

I have a linked tab delimited text file that will be updated monthly from a source outside my group, with the new file written to the LAN. I have an Access 2007 database I am using to link back to that file, which is then the source of several queries. One field, [Job Code], has both alpha and numeric values (A247, 709, L432, SS954) of varying lengths.

I have a query I run that needs to look at only job codes less than 1000. I found a function below on line as a way to strip these values down to only their numeric component (please see attached code), and then tried to use <1000 as the critiera. The query will run and return values, but within about 5 to 10 seconds, the "Data Type Mismatch..." error appears and all of the values change to a #NAME? value within the result set.

**Some things of note that may help are: 1) I only need the values that contain numeric values only. If the string has an alpha character, it can be excluded 2) I've tried the Val(...) and CInt(...) functions and got the same issue 3) There are no blank values in the Job Code field 4) Character values will only be in the first two characters of the Job Code field ***

I'm hoping for a workaround or some guidance on what the underlying issue is so that I can simply filter these records in the actual query, which will be used to write data to other databases for various departments within our organization that aren't allowed to see data from other departments.

Public Function NumericValue( strFullValue As String) as String Dim lngLoop As Long Dim strReturn As String For lngLoop = 1 To Len(strFullValue) If IsNumeric(Mid(strFullValue,lngLoop,1)) Then strReturn = strReturn & Mid(strFullValue,lngLoop,1) End If Next lngLoop NumericValue = strReturn End Function**********************I then call it in the query asJCode: NumericValue([Job Code])

Public Function NumericValue( strFullValue As String) as Long Dim lngLoop As Long Dim strReturn As Long For lngLoop = 1 To Len(strFullValue) If IsNumeric(Mid(strFullValue,lngLoop,1)) Then strReturn = strReturn & Mid(strFullValue,lngLoop,1) End If Next lngLoop NumericValue = strReturn End Function

one way to test to see if it is the function, or the file is to temporarily import the text file into access as a regular local table, then run the same query with the same function on that to see if you get the same error.