Web Development

Ask something
Question Locked

MS Access 2007

0 helpful
2 replies

by hsackner 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

2 replies

by maevinn

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.

This discussion is closed to new replies.