Anthony, your symptoms indicate a problem with the way JET (the query engine
in Access) is understanding your data. It could be a corruption, or it could
be a bug in JET.
If the problem only occurs with a particular record, it is a corruption. You
might even be able to see the problem if you look at that record directly in
the table.
If the problem occurs regardless of which record you filter, it is a JET
bug. There is one I know of that does this, but only if you are joining
table on fields that are not indexed. If you have struck this bug, you may
be able to work around the problem by adding indexes to your table fields.
If that does not make sense, or is not productive, post the SQL statement
for the query that is the RecordSource of your report. For each "table" in
this query statement, indicate if it is a local Access table, attached table
(if so, what kind of database), or another query. Also indicate the data
types of both fields involved in each JOIN.
If you wanted to demonstrate the particular bug I referred to, these steps
should do it:
1. Create a new table, with 2 fields:
MyNum Number (Long Integer)
MyMemo Memo
In the lower pane of table design, make sure the Indexed property is No for
MyNum.
Save as Table1, answering NO to the question about primary key.
2. Switch to Datasheet view.
Enter 3 records:
1 one
2 two
3 three
Close.
3. Create another table, with one field:
MyNum Number (Long Integer)
It does not matter if this is primary key or not.
Save as Table2. (It does not matter if it is primary key or not.)
Close.
4. Enter matching records:
1
2
3
Close.
5. Create a new query.
Switch to SQL View, and paste in this statement:
SELECT Table1.MyNum, Table1.MyMemo
FROM Table1 INNER JOIN Table2 ON Table1.MyNum = Table2.MyNum
GROUP BY Table1.MyNum, Table1.MyMemo;
6. Switch the query to Datasheet view. YOu see nonsense characters in the
second field of the query.
