“distinct” values

Topic

New Reply

I’m using Access 2000, and selecting distinct values from a table to use in a combo box. It’s the combination of 3 fields, the first 2 are text, the 3rd is numeric. The sql looks like this:
SELECT DISTINCT pcn.cost_center_code, pcn.fund, pcn.fund_num
FROM pcn;

Seems very straight forward to me. But the results won’t show the 3rd field, fund_num. It appears there is a row for it, but the number doesn’t show up in the results. Here is an example of the results.

12100 D
12100 E
12100 E

They should be
12100 D 0
12100 E 0
12100 E 3

I can understand why maybe the 0’s wouldn’t show up, but what about the other numbers?

Has anyone had any similar problem? I’m not sure what to try. (The field has to stay numeric. I can’t change it in the table to be text.)

Thanks. Frances Donahoe

Reply | Quote

Replies

In the properties box on the form field ensure that the Column Count = 3, that the Column Widths have 3 values ie 3;3;3 and the bound column is the correct field.

Reply | Quote

I’m sorry. I guess I didn’t explain very well. When I saw that the 3rd field wasn’t being displayed in the combo box, I went in and wrote a new query to test it, and I get the same results. Right out of the query window. At this point I’m not even looking at the combo box, just the query results. Thanks.

Reply | Quote

Are you sure you have the Show checked for that field in the query? The fact that your query is displaying this …

12100 D
12100 E
12100 E

… with the Distinct keyword says that it is, in fact, reading the third field. Otherwise, you wouldn’t see “12100 E” twice.

Reply | Quote