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 as

JCode: NumericValue([Job Code])

Open in new window

UTSWPedsBudget ManagerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Gustav BrockConnect With a Mentor CIOCommented:
Try with:

JCode: NumericValue(Nz([Job Code]))

/gustav
0
 
Rey Obrero (Capricorn1)Commented:
try using < "1000" as the criteria
0
 
UTSWPedsBudget ManagerAuthor Commented:
Same issue...plus during the 5-10 seconds when I did get a result set, it showed records that were greater than 1000.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try changing the function to return  Long instead of string

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

Open in new window



then, use this criteria

  < 1000


0
 
Patrick TallaricoFSEP Systems AnalystCommented:
In the query where you are using this code, are there any other tables joined to the text file table?  
0
 
UTSWPedsBudget ManagerAuthor Commented:
Still the same issue, unfortunately.

My thoughts wander to whether or not this is an issue with the link back to the text file.  I've updated the link and opened the file in wordpad and it appeared fine, however.
0
 
HainKurtSr. System AnalystCommented:
what is the query that you run
0
 
UTSWPedsBudget ManagerAuthor Commented:
No other tables joined to this file.

0
 
UTSWPedsBudget ManagerAuthor Commented:
The query is basically a select * from the file.  At this stage, I'm just trying to create smaller datasets for distribution to departments from the master file.  

The only field I do anything at all with is the Job Code, which I run the function against to capture the numeric values.
0
 
HainKurtSr. System AnalystCommented:
can you please post a sample db containing the table with sample data, function that you use plus a sample query to show the issue...
0
 
Patrick TallaricoConnect With a Mentor FSEP Systems AnalystCommented:
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.
0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
check this, it is working fine
SELECT Numericvalue(JobCode) AS JobNo, JobCode, Jobname
FROM Jobs
WHERE Numericvalue(JobCode)<500;


with this function in Module 1

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

Open in new window

JobCodes.mdb
0
 
UTSWPedsBudget ManagerAuthor Commented:
The NZ function worked perfectly, but I gave points to other contributors as well for effort and that given the information they had at hand, their solutions were likely workable as well.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.