Access Not Retrieving Correct Records Due to Criteria Issue

[astronomicalguy]

My Select query has two tables -- MASTER and TCDS_Data. The two tables are left-joined on MASTER.[MFR MDL CODE] = TCDS_Data.CODE. The query has five fields -- MASTER.[N-NUMBER], MASTER.[MFR MDL CODE], MASTER.[SERIAL NUMBER], TCDS_Data.tcdsSerialStart1, and TCDS_Data.tcdsSerialEnd1. The Criteria in the SERIAL NUMBER field is "Between [tcdsSerialStart1] And [tcdsSerialEnd1]" (without the quotes), so the query seeks to return all records where SERIAL NUMBER falls between tcdsSerialStart1 and tcdsSerialEnd1 for a given MFR MODEL CODE. All of the fields in all tables are Short Text formatted.



Here's the problem: For TCDS_Data.CODE= "1151410", the serial number range is CE-1 to CE-179. The query should return 116 records from MASTER, but it is only returning the records where the serial number is CE-1* -- it won't return any records where the first number following "CE-" is 2, 3, 4, 5, 6, 7, 8, or 9. In other words, it won't return the records matching serial numbers CE-2 or CE-90 even though CE-2 and CE-90 fall between CE-1 and CE-179. Access thinks that CE-2 and CE-90 are bigger than CE-179 since "2" and "9" are bigger than "1" in the first character after "CE-". Access is treating the alphanumeric more like a number instead of text, even though the field is formatted as Short Text and the data is, by definition, alphanumeric.

How do I get Access to treat the data as text and evaluate the entire field, versus treating it as a number and evaluating just the first numeric digit? In other words, how do I get Access to acknowledge that CE-2 falls between CE-1 and CE-179, and not outside of it?

Here is the query:

SELECT MASTER.[N-NUMBER], MASTER.[MFR MDL CODE], MASTER.[SERIAL NUMBER], TCDS_Data.tcdsSerialStart1, TCDS_Data.tcdsSerialEnd1
FROM MASTER LEFT JOIN TCDS_Data ON MASTER.[MFR MDL CODE] = TCDS_Data.CODE
WHERE MASTER.[SERIAL NUMBER] Between [tcdsSerialStart1] And [tcdsSerialEnd1]
ORDER BY MASTER.[N-NUMBER];

[davegri]

If all your serial numbers start with CE-
You will have to convert them (for sorting and comparison) to numbers like this example:

SELECT Master.[Serial Number], CLng(Mid([Master].[Serial Number],4)) AS Num
FROM Master
ORDER BY CLng(Mid([Master].[Serial Number],4));

The behavior you describe is an alphanumeric sort, which you don't want. The query above strips off the CE- and converts the result to a long integer and it sorts as expected for numbers.
You'll have to do the same with the start1 and end1 fields. The numeric comparisons for between will also work as expected.

The conversion in the query does not change your table data at all. The conversion occurs only in the query to get the desired sorting/comparison criteria.

[John_G]

Access is treating the alphanumeric more like a number instead of text,

No, it's the other way around. Access is treating the data as text, and not numeric at all. When it is making the comparison, it compares character by character, left to right, until it reaches the end of the shorter of the two strings. So, when comparing "CE-2" with "CE-179", it can only compare the first three characters, i.e. "CE-2" vs "CE-1", and "CE-2" IS greater than "CE-1".

Access has no way of knowing that there are numbers in there - they are just strings of characters.

This is a common problem when trying to sort numeric data stored as text, and even more of a problem when only part of the data is "numeric".

If you are able to do it, the easy fix is to store the serial numbers as fixed lengths, with leading zeros in the numeric part, e.g. "CE-009", "CE-010". Doing so will ensure that they sort and compare correctly. If the serial number is always the same format, i.e. aa-nnn, it should be easy to write an update query to reformat them.

[astronomicalguy]

Thanks, davegri and John_G.

Unfortunately, not all of the serial numbers in the table follow that same format, so I won't be able to strip the leading alphas consistently and reliably with the Mid function. For the same reason, I won't be able to consistently and reliably do the leading-zeros / fixed-length string method.

However, I think that I can beat Access at its own game. I think that I will check each character of the serial number using the Mid and IsNumeric functions. I will then concatenate all of the numeric parts into a string with "&", and then coerce the string to an integer with CInt. I'll have to do the same thing with tcdsSerialStart1 and tcdsSerialEnd1. Once everything is an integer, the [Integer] Between [Integer1] And [Integer2] should work. I'll report back.

Thanks again for the ideas.

[John_G]

Does the serial number always contain a dash, followed by digits? If so, the expression to get the number part will be quite a bit simpler.

[astronomicalguy]

Unfortunately, no. Here is a sample of varying serial numbers throughout the table: 5334, A28, 79-030, T18208245, BG-72, 210Q58839, 15AC-2, 108-3741. It's all over the map. Hence, the lack of a single, clean solution. I am making headway with the solution that I proposed -- just haven't completed it yet.

[orange]

However, I think that I can beat Access at its own game.

Access thinks that CE-2 and CE-90 are bigger than CE-179

And Access is correct, and so are all other dbms's.

Trying to sort a text field as a number is not something specific to Access. And you are not the first to discover this.

Part of the issue is the variations in format for a field that you seem to be treating as common across your application. That seems to be the root of the issue.

I'm not sure how far along you are in your project, but this design issue seems to be something that should be addressed. You can certainly create workarounds for a design issue, but it will be forever. Perhaps your requirements have changed, or perhaps the analysis was not thorough.
You know more about the project than any reader, so it is you who should make any decisions.

Good luck.

[John_G]

I think that I will check each character of the serial number using the Mid and IsNumeric functions. I will then concatenate all of the numeric parts into a string with "&", and then coerce the string to an integer with CInt.

Some are easy enough, but what is something like "79-030" supposed to yield - 30 or 79030? Similarly, what is "15AC-2" - 2 or 152?

Using what you have described, "15AC-2", "A-152", "15BC-2A" and "15B-2X" will all yield 152, which probably is not what you want.

How many different manufacturer model codes (MFR MODEL CODE) are there to deal with?

[John_G]

There may be a way to deal with this. If the serial number range is being entered through a form (as it should be!), then input masks could be applied to the two controls, and the masks could be changed according to a selected manufacturer. The Manufacturer codes and corresponding masks could be kept in a (possibly separate) table.

VBA would then put together the query SQL where clause for you, with the proper where clause. How are you using the query?

[astronomicalguy]

orange, I have absolutely not control over the data in the SERIAL NUMBER field. The serial-number formats are what they are. They come from an external source completely out of my control. I have to play the hand that I've been dealt.

[astronomicalguy]

John_G, agreed that there is a risk in extracting the alpha characters and getting the same result depending on the starting serial number. However, for a given MFR MODEL CODE, there shouldn't be any corresponding serial numbers that would cause this condition. Thorough testing will reveal the weaknesses of the approach.

To answer your other question, there are over 85,000 unique MFR MODEL CODE in the MASTER table.

[astronomicalguy]

The data is being imported from an external source.

[orange]

Who assigns the serial number?
Perhaps you could tell us clearly about the Manufacturers and Models. What a Code is? And what a serial number represents to your organization? TCDS? Master?

What is your role in this database?

[John_G]

To answer your other question, there are over 85,000 unique MFR MODEL CODE in the MASTER table.

Ah, not very practical. Well it was a thought.

Since all serial numbers for any one MFR MODEL CODE should be the same format, maybe you could look at extracting your numbers by working backwards from the end of the serial number until you reach a non-numeric character, and then using the digits found as the number. It might make a bit easier for the users.

Function Serial(strSerial As String) As Long
  Dim J As Integer 'loop counter
  J = Len(strSerial)
  
  '
  ' Return -1 if last character is not numeric
  '
  If Not IsNumeric(Mid(strSerial, J, 1)) Then
    Serial = -1
    Exit Function
  End If
    
  Do Until Not IsNumeric(Mid(strSerial, J, 1))
    J = J - 1
    If J = 0 Then Exit Do
  Loop
  
  J = J + 1  ' J is position of first of the trailing digits
  
  Serial = Val(Mid(strSerial, J))
  
End Function

[astronomicalguy]

I'm one error away from having the character-by-character evaluation working. The only problem is that I'm getting a parameter error with no parameters defined (i.e. Access claims to not be able to find the parameter in the query). Here is the new query (shortened-for-clarity and using "1151410" as the MFR MODEL CODE for testing):

SELECT MASTER.[MFR MDL CODE],
IIf(Len([SERIAL NUMBER])>=1,IIf(IsNumeric(Mid([SERIAL NUMBER],1,1)),Mid([SERIAL NUMBER],1,1),""),"") & ... & IIf(Len([SERIAL NUMBER])>=5,IIf(IsNumeric(Mid([SERIAL NUMBER],5,1)),Mid([SERIAL NUMBER],5,1),""),"") AS [SERIAL NUMBER LEFT],
IIf(Len([SERIAL NUMBER])>=6,IIf(IsNumeric(Mid([SERIAL NUMBER],6,1)),Mid([SERIAL NUMBER],6,1),""),"") & ... & IIf(Len([SERIAL NUMBER])>=10,IIf(IsNumeric(Mid([SERIAL NUMBER],10,1)),Mid([SERIAL NUMBER],10,1),""),"") AS [SERIAL NUMBER RIGHT],
CInt([SERIAL NUMBER LEFT] & [SERIAL NUMBER RIGHT]) AS [SERIAL NUMBER WHOLE], MASTER.[SERIAL NUMBER], TCDS_Data.CODE
FROM MASTER LEFT JOIN TCDS_Data ON MASTER.[MFR MDL CODE] = TCDS_Data.CODE
WHERE (((MASTER.[MFR MDL CODE])="1151410"))
ORDER BY CInt([SERIAL NUMBER LEFT] & [SERIAL NUMBER RIGHT]);

If I run the query without the ORDER BY, it works fine. But if I manipulate the [SERIAL NUMBER WHOLE] field in any way, either with ORDER BY or, more importantly, putting something in Criteria, then Access throws up the parameter dialogue asking for [SERIAL NUMBER LEFT] and [SERIAL NUMBER RIGHT].