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)
NumericValue = strReturn
I then call it in the query as
JCode: NumericValue([Job Code])