Web Development



MS Access 2007

By hsackner ·
I have set up a macro that imports data into 2 tables.
The query I run creates a new table that stores for matching amounts.

I don't get alll the matches in the new table even though they match.

If I go into each table a reenter the amounts manually this resolves the problem

I don't want to manually enter the amounts.

What can be the issue causing this problem?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Taking a stab at this..

by Maevinn In reply to MS Access 2007

The macro pulls the data from one source and parses some fields into one table, some fields into a different table. The query to create a new table recombines those split fields into a third table...And sometimes it doesn't capture all the records, but if you manually re-enter the same data, it does identify them correctly.

At a guess, the data that is being imported sometimes includes unseen characters (ie, spaces, hard returns) and that's what's preventing Access from matching them up. When you re-enter, you don't put in those non-printing values, so the fields match.

To fix, use the Trim() function on the fields in question during the import process. I'd also run validation to prevent leading spaces from being entered, or remove them with the Trim as well.

Related Discussions

Related Forums