Problem with DISTINCT query ?

[Farmer]

Hello Folks,
can anybody tell me how to do the following very simple thing:
I have a table that has say departments, divisions and units of an
organisation and what i am trying to do is find a divison. One
department has many divisions, and one division many units. I am think
therefore a SELECT DISTINCT query on the division would make sense and
to put my search criteria for department on the WHERE. Of course that
doesnt work since you need the where clause to refer to a field in the
SELECT DISTINCT clause and if I put the field there it would make that
distinct as well. I know it must be simple but i acnnot figure out how
to do it.

[Farmer]

In fact I can put the criteria in the WHERE even if they dont appear in
the SELECT (i just tried it in access Query design). Why then does it
not work when I put exactly the same in me.form.filter ??? (and it is
not the filteron = true or the quotation marks)

[Farmer]

Right to be more precise with the problem: here is the query
on the form's record source:

SELECT DISTINCT tlkpLocationDirectory.chrL4Description FROM
tlkpLocationDirectory ORDER BY tlkpLocationDirectory.chrL4Description;

and here is the problematic code:

Me.Form.FilterOn = False
strWhere = "(((tlkpLocationDirectory.chrL2Description)="" " &
cboL2D.Value & """" & "))"
Me.Form.Filter = strWhere 'at this point the all too hostile enter
parameter box comes up and requests the value of
tlkpLocationDirectory.chrL2Description
Me.FilterOn = True
What did i do wrong? I got the query from the query design and it was
running.

[Rob Parker]

The problem is that your form's recordset does not contain chrL2Description.

In another of your posts you say that this works in a query. That's because
the recordset for the query is the table, and so any of its fields are
available. In your form, you've limited the recordset to a single field,
and that's all that it knows about. To get it to work, you must include
chrL2Description in the form's recordset. The field does not need to be
displayed on the form; but it does need to be in the form's recordset.

HTH,

Rob

"Farmer" <kn******@gmail.com> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...

Right to be more precise with the problem: here is the query
on the form's record source:

SELECT DISTINCT tlkpLocationDirectory.chrL4Description FROM
tlkpLocationDirectory ORDER BY tlkpLocationDirectory.chrL4Description;

and here is the problematic code:

Me.Form.FilterOn = False
strWhere = "(((tlkpLocationDirectory.chrL2Description)="" " &
cboL2D.Value & """" & "))"
Me.Form.Filter = strWhere 'at this point the all too hostile enter
parameter box comes up and requests the value of
tlkpLocationDirectory.chrL2Description
Me.FilterOn = True
What did i do wrong? I got the query from the query design and it was
running.

[John Welch]

Is chrL2Description a different field than chrL4Description, or is it
spelled wrong?

"Farmer" <kn******@gmail.com> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...

Right to be more precise with the problem: here is the query
on the form's record source:

SELECT DISTINCT tlkpLocationDirectory.chrL4Description FROM
tlkpLocationDirectory ORDER BY tlkpLocationDirectory.chrL4Description;

and here is the problematic code:

Me.Form.FilterOn = False
strWhere = "(((tlkpLocationDirectory.chrL2Description)="" " &
cboL2D.Value & """" & "))"
Me.Form.Filter = strWhere 'at this point the all too hostile enter
parameter box comes up and requests the value of
tlkpLocationDirectory.chrL2Description
Me.FilterOn = True
What did i do wrong? I got the query from the query design and it was
running.

[Farmer]

L2 stands for level 2 (deparment) and L4 level 4 (branch)

[Farmer]

Thank you Rob for your reply, if I did that then L2Descriprion would de
"DISTINCT" and I don't want it to be

[Farmer]

And also in the query it was the exact same query that worked i.e.
SELECT DISTINCT tlkpLocationDirecotry.L4Description FROM...

[Farmer]

You know something Rob...
You are absolutely right and I made a silly mistake. The mistake is: If
you make a SELECT DISTINCT query on two field it will return the pair
once and not each field once. Of course that is the only way it makes
sense otherwise you would not get meaningful results. I did not
understand that very well until now, it is my first Access program.

[Rob Parker]

Glad you've got it!

Rob

"Farmer" <kn******@gmail.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...

You know something Rob...
You are absolutely right and I made a silly mistake. The mistake is: If
you make a SELECT DISTINCT query on two field it will return the pair
once and not each field once. Of course that is the only way it makes
sense otherwise you would not get meaningful results. I did not
understand that very well until now, it is my first Access program.

[David W. Fenton]

"Farmer" <kn******@gmail.com> wrote in
news:11**********************@e56g2000cwe.googlegr oups.com:

[the first of a half dozen posts threading out the description of
the problem over several articles]

Next time you want help, pause and think about how to describe it.
Then write ONE POST describing the whole problem.

Any time I see a thread with a chain of posts from the original
poster, I skip to the next thread, because I just don't have time to
sort through the confusion of assembling the multiple posts into a
coherent description of the problem.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/