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

UTSWPedsAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

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
 
UTSWPedsAuthor 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
UTSWPedsAuthor 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
 
UTSWPedsAuthor Commented:
No other tables joined to this file.

0
 
UTSWPedsAuthor 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
 
UTSWPedsAuthor 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
All Courses

From novice to tech pro — start learning today.