Missing Records in Find, but not with Filter

[Mark Chimes]

Hi All,

I have some tables in an Access 2000 MDB file with a field called "Job
Number". (This field is a foreign key into another table).

When I run a report, based on a query, I expect to see every record in this
table matching the entered Job Number.
The report is missing many records.

When I use the Find wizard (either clicking on the binoculars or pressing
Ctl+F), only some records are found. (The same records that are included in
the report).

When I scroll thru my table, I can see many records with the same Job
number, but the find process will skip many of these records as if they do
not exist.

However, if I Filter By Selection, all matching records are displayed.

If I configure Find to search in "Any part of Field" instead of the default
"Whole Field", then every record matching the Job Number is returned
correctly.

If I over-type the Job Number with it's own value, then that record is now
included in the find.
This seems to indicate that there is some sort of non-printable character
included in the Job Number field.

This data is input into an Excel spreadsheet and imported into Access. I
have checked the formatting of the Excel column (General), the data type of
the MDB tables (Double), but I cannot find a reason why some records are
correct and some are not.

Has anyone come across this behaviour before?
Any suggestions on how to resolve this would be greatly appreciated.

cheers,
Mark Chimes

 

[Guest]

My guess would be extra spaces (or tabs, but less likely)
at the end of text.

Access will truncate white space at the end of a line as
you enter data into a form or datasheet, but that is only
when you type things in.
It does not automatically truncate white space when
you import, append, or update.

You should try to run an update query on the table,
setting the new value to be
trim([fieldname])

Make a backup copy before running a big update
query like that.

(david)

 

[Mark Chimes]

Hi David,

your code suggestion resolves currently-held data but does not explain where
the erroneous data comes from, or why it is created. We have numerous
queries, reports, macros etc using this data and would rather resolve the
cause.

Since you assistance, I have discovered that if the client zips the MDB
file, emails it and I unzip it on my PC, the problem no longer exists.

In another perculiar twist, the previously successful filter by selection
now issues an "Undefined function in Cstr$ in expression" error.

I am increasingly beginning to wonder if the issue is caused by a system or
environmental setting rather than some supposedly introduced addition to the
data.

cheers,
Mark Chimes

 

[John W. Vinson]

In another perculiar twist, the previously successful filter by selection
now issues an "Undefined function in Cstr$ in expression" error.

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

John W. Vinson [MVP]