Access 2003: Datasheet and Listbox have different results when using same query

Question

I am writing an Access 2003 form to get a listing of all people currently in a facility.

I have a saved query that gives me the results I expect when I look at the Datasheet view (listing of all names with the associated account number).

If I use that same saved query as my ListBox.RowSource, I get the same list of names, but only ~10% of the names listed have an account number. tblOne.ACCOUNT is guaranteed to not be null so I'm not sure how those empty results come up.

SELECT tblOne.NAME, tblOne.ACCOUNT
FROM   tblOne INNER JOIN tblTwo ON
       tblOne.ID = tblTwo.ID
WHERE  tblOne.ENTER_DT >= (Date()-7)
  AND  tblTwo.EXIT_DT Is Null
ORDER BY tblOne.NAME

Both of the tables are linked tables. The listbox has two columns. I can not explain why a saved query is giving me different results depending on if I look at it using the Datasheet view or use it as the RowSource of a ListBox.

The errors started to come into play when I added the INNER JOIN. But it still works correctly in the Datasheet view with the JOIN statement.

Answer 1

That's mysterious. But there are some obvious trouble shooting steps. What do you get using this as a basis?

SELECT tblOne.NAME, tblOne.ACCOUNT FROM tblOne

Trouble shooting often consists of "dumbing down" until you get something that seems to work, then incrementally adding complexity until you have your desired result. Somewhere on that path, you fix your error.

Answer 2

What happens if you use SELECT DISTINCT in both your Listbox and Query ? Does that help getting the same expected result ?