whats wrong with this Distinct (1 Viewer)

dmyoungsal

Registered User.
Local time
Today, 02:00
Joined
May 1, 2016
Messages
112
I am reworking a DB to simplify maintenance for salesman if/when I leave this company. The intent is to consolidate three tables into one flat file (which will work just fine).

However, I still need to be able use three cascading combo boxes to filter the search.

My first combo box (cboBrand), has this code as RowSource SELECT DISTINCT qryPurchaseInfoNew.Make FROM qryPurchaseInfoNew;

The second combo box will filter the results of the first, but it's not showing the distinct values and I cannot figure out why. Here is the code:
SELECT DISTINCT qryPurchaseInfoNew.ID, qryPurchaseInfoNew.TracModel, qryPurchaseInfoNew.Make FROM qryPurchaseInfoNew WHERE ((qryPurchaseInfoNew.Make)=[Forms]![frmTractorPriceToolNew]![cboBrand]) ORDER BY qryPurchaseInfoNew.TracModel;
 

MarkK

bit cruncher
Local time
Today, 02:00
Joined
Mar 17, 2004
Messages
8,178
...but it's not showing the distinct values...
What does that mean exactly, "not showing the distinct values," does that mean it is showing duplicates, or that no values are showing, or something else?
Mark
 

isladogs

MVP / VIP
Local time
Today, 09:00
Joined
Jan 14, 2017
Messages
18,186
You are including the ID field which by definition will be different for each record... though it's probably hidden in your combo.
 

dmyoungsal

Registered User.
Local time
Today, 02:00
Joined
May 1, 2016
Messages
112
What does that mean exactly, "not showing the distinct values," does that mean it is showing duplicates, or that no values are showing, or something else?
Mark

Some of the models say 6230, will show three times. I want the combo boxt show 6230 only one time.
 

dmyoungsal

Registered User.
Local time
Today, 02:00
Joined
May 1, 2016
Messages
112
You are including the ID field which by definition will be different for each record... though it's probably hidden in your combo.

From another forum, it was suggested that I only use
SELECT DISTINCT qryPurchaseInfoNew.TracModel. When I do that, nothing appears in the combo box.

If I remove ID from the Select Statement, then all i see is the Brand (John Deere) repeated upto 30 times. Which is from "qryPurchaseInfoNew.Make"

Yes, the ID field will be different for each record. I may have a model (say, 6230) that are all different. once I can select the correct model using the second combo, a third combo box will allow the user to select to actual and correct model my people need to look at.
 

isladogs

MVP / VIP
Local time
Today, 09:00
Joined
Jan 14, 2017
Messages
18,186
From another forum, it was suggested that I only use
SELECT DISTINCT qryPurchaseInfoNew.TracModel. When I do that, nothing appears in the combo box.

If I remove ID from the Select Statement, then all i see is the Brand (John Deere) repeated upto 30 times. Which is from "qryPurchaseInfoNew.Make"

Yes, the ID field will be different for each record. I may have a model (say, 6230) that are all different. once I can select the correct model using the second combo, a third combo box will allow the user to select to actual and correct model my people need to look at.

First of all, if you cross post, please state you have done so and provide the link.

In order to advise further, please show screenshots of the query design and results.


EDIT When you tried the single field, TracModel, as suggested by ranman in the other forum, did you ensure that the column width was greater than zero. If not, the field will be hidden
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:00
Joined
May 21, 2018
Messages
8,463
I would build these queries and look at them in the query viewer. As you describe the problem it sounds like a formatting issue on the combo for why you are not seeing any tracmodels when you do a distinct on the just tracmodel.
Take a look at
Column Count:
Column widths:
bound column:

I am going to guess in column width there is something like :0";1" where the first column is 0 width. So you are returning the distinct values, but they are not visible.
 

Users who are viewing this thread

Top Bottom