find not finding records

[ack]

Hello,
I was asked to look into a problem with the access find box not finding
some records, when searching thru records via a form. The "missing"
record could be found if the partial name and wildcard * was used. I
checked the actual record and could find no leading or trailing
characters. It's only "sometimes" this happens, and the records which
are not found appears to be totally random. I wanted to check here,
first, before spending a lot of time looking into this intermittent
problem, as I really wouldn't know where to start digging, beyond the one
thing I looked for, above.
Most of of the info returned by google searches focused on queries and
improper syntax, but this strictly a find box issue.
Any and all possible issues please let me know.
Thanks,
Ack

 

[John Spencer]

It is probably not a find box issue. It is probably a data issue.
My guess is that some of the data in the fields has invisible (to the eye)
trailing characters. They could even be spaces if the data was input using
code or imported from other sources.

You can test for trailing spaces with a query like the following which
should return records where there are trailing spaces.

Field: SuspectField
Criteria: <> Trim([SuspectField])

The SQL text might look like (where the * will return the entire record.
SELECT [SuspectField], *
FROM [YourTableName]
WHERE [SuspectField] <> Trim([SuspectField])

 

[andy]

My guess is that some of the data in the fields has invisible (to the
eye) trailing characters.

Hi John,
Just to clarify for me, when I say I checked the data for trailing or
leading characters: For one of the cluprit records, I went into the table
and clicked on the field, and hitting home and end moved the cursor to
the left and right of the data, with no spaces between the cursor and the
data. Is this not a good enough check to see if there are extra
characters? It's possible that there are hidden characters beyond the
end of the cursor, which is at the end of the word?

Thanks for your help and query, but please confirm that my above test was
useless, as I've been using it for years, with other db's, to make sure
imported data didn't have trailing and leading characters, on a random
sampling of data.
Ack

 

[Guest]

You can have non-printing character and they do not occupy horizontal space
in the display. You can also have a line feed (entered from the keyboard
using CTRL-Enter) and more text. Use the right cursor key or expand the row
display size vertically by dragging it down.

 

[andy]

in

Thanks for the info, I'll certainly try your query; never thought that
there could be page breaks, etc, in the data.

 

[John Spencer]

If you are talking about the query I proposed, then it will not find fields
with non-printing characters other than spaces. The trim function only
removes leading and trailing spaces. I believe your test should have shown
you that

You could use the Len function to see what length Access says the string is.
If you see 8 and Access says more than that, then you know that there are
extra characters in the string.