ms-access: listbox is not displaying data

Question

the row source for a listbox looks like this:

SELECT users.id, users.first, users.last, chavrusas.luser_type AS user_type, chavrusas.id, users.title, users.city, users.state, users.home_phone, users.email  FROM Users INNER JOIN chavrusas ON Users.id=chavrusas.luser_id  WHERE ((chavrusas.ruser_id)=id_txt and chavrusas.ended=false) AND (chavrusas.luser_type)<>(chavrusas.ruser_type) AND NOT ((chavrusas.luser_type)='teacher' AND (chavrusas.ruser_type)='student') AND NOT ((chavrusas.ruser_type)='teacher' AND (chavrusas.luser_type)='student'); UNION SELECT users.id, users.first, users.last, chavrusas.ruser_type AS user_type, chavrusas.id, users.title, users.city, users.state, users.home_phone, users.email  FROM Users INNER JOIN chavrusas ON Users.id=chavrusas.ruser_id  WHERE ((chavrusas.luser_id)=id_txt and chavrusas.ended=false) AND (chavrusas.luser_type)<>(chavrusas.ruser_type) AND NOT ((chavrusas.luser_type)='teacher' AND (chavrusas.ruser_type)='student') AND NOT ((chavrusas.ruser_type)='teacher' AND (chavrusas.luser_type)='student')
ORDER BY 4;

for some reason, when there are more than just a few items that this statement returns, the listbox will display null values!! but if this statement returns 2-3 items, it will display them no problem.

Answer 1

1) Does the query work when you run it in the query designer?

2) Are the number of fields in the query the same number as is in the listbox Column Count property?

3) What is the 4 in "ORDER BY 4"? 4 should be a field name such as users.last or user_type.

4) First and Last are the names of functions used in queries. So this will likely cause you problems in the future if it isn't already. Put square brackets around the field names or use different field names. Also see Tony's Table and Field Naming Conventions