Querys wont find specific serial numbers.

  • Thread starter Thread starter Lee Michon
  • Start date Start date
L

Lee Michon

We noticed this recently, when I do a query for a specific
serial number, the item wont show up in the query results.
Most serial numbers will show up with the search..but we
have found maybe 5 or so that wont(in 2000+entrys).

I go to the actual entry in the DB and copy the serial
number then paste it into the query... and it doesnt find
anything. However, it is cleary there.

If I use the "like" function in the query like this for
instance:
like "*"+"serialnumber"+"*" then it will show up.

The entry will show up if I do a search for "product
number" or just a "show all" type of deal without
the "like" function...

I have no clue how to troubleshoot this issue.

Please help.
 
Hi Lee

I presume that you enter the criteria to search in a text box which is in turn
read by the query. The general expression in the query should be as follows:


Like IIF([forms]![MyMainForm]![MyTxt]="","*","*" & [forms]![MyMainForm]![MyTxt]
& "*")

Hope this helps

Maurice St-Cyr
Micro Systems Consultants, Inc.
Ottawa, Ontario
 
Best guess is that there is one or more non-visible special characters in the field.

You may have to re-enter the values in the fields that have "bad" data.

How you do that could well depend on the structure of your Serial Numbers. Are
they all numbers? Are they fixed length? etc.

You might use a user VBA function to strip out all non-legitimate (by your
rules) characters and use that in an update query to reset the serial numbers.
It could be that one of the users is typing a special character accidentally.

Hope this helps get you started.
 
Thats a good suggestion, when I look at the fields, I cant
see any special caracters though (I know you said they
would be hidden). I did try rewriting the serial number in
the form I use to do changes.... no affect.

However, I tried to update it with an append query with
the same serial number that I typed in and now it shows up
in query searches.

Each serial number is different... field is Text and the
serial numbers have both numbers and letters of varying
length.

At least I have somewhat of a fix right now.

Thanks.

Anymore ideas, suggestions are welcomed.
-----Original Message-----
Best guess is that there is one or more non-visible
Click to expand...
special characters in the field.
 
Any Spaces in the serial number.

TRY this on a COPY of your database.

First create a function in a module. UNTESTED

Public Function StripToCharsOnly(ByVal varText As Variant) As String
'Takes input and returns only the specified characters in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

'Define allowable characters in the constant
Const strChars As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = ""

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strChars, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

StripToCharsOnly = strOut

End Function

Once you've saved that in a module, you can call it in an Update query.


UPDATE YourTable
Set YourSNField = StripToCharsOnly(YourSnField)
WHERE YourSNField is Not Null
 
Back
Top