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 tlkpLocationDir ectory.chrL4Des cription FROM
tlkpLocationDir ectory ORDER BY tlkpLocationDir ectory.chrL4Des cription;

and here is the problematic code:

Me.Form.FilterO n = False
strWhere = "(((tlkpLocatio nDirectory.chrL 2Description)=" "" &
cboL2D.Value & """" & "))"
Me.Form.Filter = strWhere 'at this point the all too hostile enter
parameter box comes up and requests the value of
tlkpLocationDir ectory.chrL2Des cription
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 chrL2Descriptio n.

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
chrL2Descriptio n 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.goo glegroups.com.. .

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

SELECT DISTINCT tlkpLocationDir ectory.chrL4Des cription FROM
tlkpLocationDir ectory ORDER BY tlkpLocationDir ectory.chrL4Des cription;

and here is the problematic code:

Me.Form.FilterO n = False
strWhere = "(((tlkpLocatio nDirectory.chrL 2Description)=" "" &
cboL2D.Value & """" & "))"
Me.Form.Filter = strWhere 'at this point the all too hostile enter
parameter box comes up and requests the value of
tlkpLocationDir ectory.chrL2Des cription
Me.FilterOn = True
What did i do wrong? I got the query from the query design and it was
running.

[John Welch]

Is chrL2Descriptio n a different field than chrL4Descriptio n, or is it
spelled wrong?

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

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

SELECT DISTINCT tlkpLocationDir ectory.chrL4Des cription FROM
tlkpLocationDir ectory ORDER BY tlkpLocationDir ectory.chrL4Des cription;

and here is the problematic code:

Me.Form.FilterO n = False
strWhere = "(((tlkpLocatio nDirectory.chrL 2Description)=" "" &
cboL2D.Value & """" & "))"
Me.Form.Filter = strWhere 'at this point the all too hostile enter
parameter box comes up and requests the value of
tlkpLocationDir ectory.chrL2Des cription
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 tlkpLocationDir ecotry.L4Descri ption 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******** *************@i 40g2000cwc.goog legroups.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.