Add ALL to combobox

[Neil M]

I've looked on lots of sites for this and been directed to others but I
still do not understand how this works?

Can anyone explain how to do it it lame man's terms please?

Thanks.

 

[Douglas J Steele]

There are two separate approaches, depending on where your combobox is based
on a query or on a values list, given at
http://www.mvps.org/access/forms/frm0043.htm at "The Access Web"

Dev's explanation looks pretty thorough to me. What part don't you
understand?

 

[Neil M]

I use the code like this;

With Me.Combo0
.RowSourceType = "Value List"
.RowSource = "(All);" & .RowSource
End With

But when i select the pulldown list it has ALL and the name of my query
I have attempted to change the code etc etc but nothing appears to work?

 

[Graham R Seach]

Neil,

You can have either a value list OR a table/query - not both! So if your
combo is based on a query, you can't use:
.RowSource = "(All);" & .RowSource

....you'll have to set RowSourceType = Table/Query, then append a UNION query
to your RowSource query. For example:
SELECT CustomerID, CompanyName
FROM Customers
UNION (SELECT Null as CustomerID, "(All)" As CompanyName
FROM Customers)
ORDER BY CustomerID;

Naturally, change the outer query to reflect the table and field names you
have.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Neil M]

ok thanks but the coding is where i think i'm going wrong, so can you help
correct my mistakes?

My Table is 'TBL_Issues'
My combobox is labelled 'cboLearner'
Field what I need to add ALL to is 'Learner'

Out opf the code below what are the tables, fields and that?
CustomerID, CompanyName, Customers?

I don't quite understand how I can put a field and table in the code where
it looks like there are 3 different items I need to input (CustomerID,
CompanyName, Customers?)??

Thanks. I'm sure I will get there in the end, with a little help!!

 

[Neil M]

Currently my coding looks like this (Union Query);

SELECT Learner, IssueNumber FROM TBL_Issues UNION (SELECT Null as Learner,
"(All)" As IssueNumber FROM TBL_Issues);

But that just shows me a list of all learners (duplicates as well) and no
ALL at the top.

 

[Douglas J Steele]

That's very odd. The fact that you're using UNION, rather than UNION ALL,
means that all duplicates should be eliminated.

Of course, if a Learner can have multiple IssueNumbers associated, then you
will get apparent duplicates, as it will only eliminate duplicate
combinations of Learner and IssueNumber: the same Learner will appear
multiple times, once for each IssueNumber associated with that Learner.

As to why ALL isn't at the top, if what appears in your combobox is the
Learners, you've put (All) in the wrong place in your second SELECT query:
you need it to be the same column as Learner:

SELECT Learner, IssueNumber FROM TBL_Issues
UNION
SELECT "(All)" as Learner, Null As IssueNumber FROM TBL_Issues

For what it's worth, it's actually not critical that you alias the fields in
the second or subsequent SELECTs in a UNION query: the field names from the
first SELECT are what's picked as the query's fields, and the position of
the fields in the subsequent SELECTs is all that matters. In other words,
you could have used

SELECT Learner, IssueNumber FROM TBL_Issues
UNION
SELECT "(All)", Null FROM TBL_Issues

or even

SELECT Learner, IssueNumber FROM TBL_Issues
UNION
SELECT "(All)" AS Tom, Null AS M FROM TBL_Issues

 

[Neil M]

right thanks. i think i'm beginning to understand although I have tried all
the code below
and get the same results...
it shows ALL on the top of the list, duplicates the names (each name has a
different issue number associated with it - how can i get rid of duplicate
names?) but when i run the query that links the comboboxes (Learner,
Responsible and Status) it shows no records?

I think it's the method I'm using rather than the coding - but how would I
get the 3 comboboxes (which are linked to one query linked to a report) to
display my report criteria including ALL.
I need to put ALL on all 3 comboboxes so that I can print out a report of
everything at once also.

Thanks for your help thus far..
Least I'm learning as I go!

 

[Douglas J Steele]

If all you want is the individual learners, then you probably want

SELECT Learner FROM TBL_Issues
UNION
SELECT "(All)" FROM TBL_Issues

To get All in 3 separate comboboxes, you'd have to do the same sort of thing
to each of them.

Note that putting (All) in the combobox alone isn't enough to get everything
selected for your report, though. Assuming that your query is pointing to
those 3 comboboxes, so that you can limit what's selected when you want to,
you'll have to modify your query to handle (All).

If your query has something like:

WHERE Learner = Forms!MyForm!cboLearner

in it, you're going to need something like:

WHERE (Learner = Forms!MyForm!cboLearner OR Forms!MyForm!cboLearner =
'(All)')

 

[Neil M]

super - it appears the combo box lists now work and I am using this code for
each (linked individually to the union query)

cboLearner:
SELECT Learner, Learner FROM TBL_Issues
UNION SELECT "(All)" AS Tom, Null AS M FROM TBL_Issues;

cboResponsible:
SELECT Responsible, Learner FROM TBL_Issues
UNION SELECT "(All)" AS Tom, Null AS M FROM TBL_Issues;

cboStatus:
SELECT Status, Learner FROM TBL_Issues
UNION SELECT "(All)" AS Tom, Null AS M FROM TBL_Issues;


Now the problem appears to be within the query...
I have the report linked to the query as below; (Qry_LRS)

Learner field:
Criteria: [Forms]![Main]![cboLearner]
Or: ="(All)"

Responsible field:
Criteria: [Forms]![Main]![cboResponsible]
Or: ="(All)"

Status field:
Criteria: [Forms]![Main]![cboStatus ]
Or: ="(All)"

I attenpted your coding but it didn't appear to work.
Can you see where I am going wrong...

Thanks.

 

[Neil M]

Right I have got it now working partly;
I changed the query code to look like this for each:

Criteria: [Forms]![Main]![cboResponsible]
OR: [Forms]![Main]![cboResponsible]="(All)"

Which was weird cause that didn't work first of all.

But now I have the probloem that it only displays All when I select ALL on
all 3 comboboxes.
If I decide to select Learners = ALL, Named Responsible and Status = Open,
it does not display
I can't seem to pinpoint

Thanks.
Neil

 

[Douglas J Steele]

Sounds as though you're working with the query-building grid.

For something like this, it's much better to work directly with the SQL.
WIth the query open, select SQL View from the View menu.

Copy and paste your SQL below, and I'll take a look at it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Right I have got it now working partly;
I changed the query code to look like this for each:

Criteria: [Forms]![Main]![cboResponsible]
OR: [Forms]![Main]![cboResponsible]="(All)"

Which was weird cause that didn't work first of all.

But now I have the probloem that it only displays All when I select ALL on
all 3 comboboxes.
If I decide to select Learners = ALL, Named Responsible and Status = Open,
it does not display
I can't seem to pinpoint

Thanks.
Neil

If all you want is the individual learners, then you probably want

SELECT Learner FROM TBL_Issues
UNION
SELECT "(All)" FROM TBL_Issues

To get All in 3 separate comboboxes, you'd have to do the same sort of thing
to each of them.

Note that putting (All) in the combobox alone isn't enough to get everything
selected for your report, though. Assuming that your query is pointing to
those 3 comboboxes, so that you can limit what's selected when you want to,
you'll have to modify your query to handle (All).

If your query has something like:

WHERE Learner = Forms!MyForm!cboLearner

in it, you're going to need something like:

WHERE (Learner = Forms!MyForm!cboLearner OR Forms!MyForm!cboLearner =
'(All)')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tried
all

has

a

would

I

report)

to position
of others
but

 

[Neil M]

SELECT Learner, Learner FROM TBL_Issues
UNION SELECT "(All)" AS Tom, Null AS M FROM TBL_Issues;

Sounds as though you're working with the query-building grid.

For something like this, it's much better to work directly with the SQL.
WIth the query open, select SQL View from the View menu.

Copy and paste your SQL below, and I'll take a look at it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Right I have got it now working partly;
I changed the query code to look like this for each:

Criteria: [Forms]![Main]![cboResponsible]
OR: [Forms]![Main]![cboResponsible]="(All)"

Which was weird cause that didn't work first of all.

But now I have the probloem that it only displays All when I select ALL on
all 3 comboboxes.
If I decide to select Learners = ALL, Named Responsible and Status = Open,
it does not display
I can't seem to pinpoint

Thanks.
Neil

If all you want is the individual learners, then you probably want

SELECT Learner FROM TBL_Issues
UNION
SELECT "(All)" FROM TBL_Issues

To get All in 3 separate comboboxes, you'd have to do the same sort of thing
to each of them.

Note that putting (All) in the combobox alone isn't enough to get everything
selected for your report, though. Assuming that your query is pointing to
those 3 comboboxes, so that you can limit what's selected when you

want
to,

you'll have to modify your query to handle (All).

If your query has something like:

WHERE Learner = Forms!MyForm!cboLearner

in it, you're going to need something like:

WHERE (Learner = Forms!MyForm!cboLearner OR Forms!MyForm!cboLearner =
'(All)')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


right thanks. i think i'm beginning to understand although I have tried
all
the code below
and get the same results...
it shows ALL on the top of the list, duplicates the names (each name

has

a
different issue number associated with it - how can i get rid of duplicate
names?) but when i run the query that links the comboboxes (Learner,
Responsible and Status) it shows no records?

I think it's the method I'm using rather than the coding - but how

would

I
get the 3 comboboxes (which are linked to one query linked to a

report)

to
display my report criteria including ALL.
I need to put ALL on all 3 comboboxes so that I can print out a

report
of

everything at once also.

Thanks for your help thus far..
Least I'm learning as I go!

That's very odd. The fact that you're using UNION, rather than UNION
ALL,
means that all duplicates should be eliminated.

Of course, if a Learner can have multiple IssueNumbers associated, then
you
will get apparent duplicates, as it will only eliminate duplicate
combinations of Learner and IssueNumber: the same Learner will appear
multiple times, once for each IssueNumber associated with that Learner.

As to why ALL isn't at the top, if what appears in your combobox

is
the

Learners, you've put (All) in the wrong place in your second SELECT
query:
you need it to be the same column as Learner:

SELECT Learner, IssueNumber FROM TBL_Issues
UNION
SELECT "(All)" as Learner, Null As IssueNumber FROM TBL_Issues

For what it's worth, it's actually not critical that you alias the
fields
in
the second or subsequent SELECTs in a UNION query: the field names from
the
first SELECT are what's picked as the query's fields, and the position
of
the fields in the subsequent SELECTs is all that matters. In other
words,
you could have used

SELECT Learner, IssueNumber FROM TBL_Issues
UNION
SELECT "(All)", Null FROM TBL_Issues

or even

SELECT Learner, IssueNumber FROM TBL_Issues
UNION
SELECT "(All)" AS Tom, Null AS M FROM TBL_Issues


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Currently my coding looks like this (Union Query);

SELECT Learner, IssueNumber FROM TBL_Issues UNION (SELECT Null as
Learner,
"(All)" As IssueNumber FROM TBL_Issues);

But that just shows me a list of all learners (duplicates as

well)
and

no
ALL at the top.



I've looked on lots of sites for this and been directed to others
but
I
still do not understand how this works?

Can anyone explain how to do it it lame man's terms please?

Thanks.

 

[Neil M]

oops last one was wrong code, here it is;


SELECT TBL_Issues.Responsible, TBL_Issues.Status, TBL_Issues.Programme,
TBL_Issues.IssueNumber, TBL_Issues.Issue, TBL_Issues.Recommendation,
TBL_Issues.DateRaised, TBL_Issues.DateCompleted, TBL_Issues.Comments
FROM TBL_Issues
WHERE (((TBL_Issues.Responsible)=[Forms]![Main]![cboResponsible]) AND
((TBL_Issues.Status)=[Forms]![Main]![cboStatus]) AND
((TBL_Issues.Learner)=[Forms]![Main]![cboLearner])) OR
((([Forms]![Main]![cboResponsible])="(All)") AND
(([Forms]![Main]![cboStatus])="(All)") AND
(([Forms]![Main]![cboLearner])="(All)"))
ORDER BY TBL_Issues.IssueNumber;

SELECT Learner, Learner FROM TBL_Issues
UNION SELECT "(All)" AS Tom, Null AS M FROM TBL_Issues;

Sounds as though you're working with the query-building grid.

For something like this, it's much better to work directly with the SQL.
WIth the query open, select SQL View from the View menu.

Copy and paste your SQL below, and I'll take a look at it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Right I have got it now working partly;
I changed the query code to look like this for each:

Criteria: [Forms]![Main]![cboResponsible]
OR: [Forms]![Main]![cboResponsible]="(All)"

Which was weird cause that didn't work first of all.

But now I have the probloem that it only displays All when I select

ALL

on pointing
to name
has

 

[Douglas J Steele]

You need to change it to

SELECT TBL_Issues.Responsible, TBL_Issues.Status, TBL_Issues.Programme,
TBL_Issues.IssueNumber, TBL_Issues.Issue, TBL_Issues.Recommendation,
TBL_Issues.DateRaised, TBL_Issues.DateCompleted, TBL_Issues.Comments
FROM TBL_Issues
WHERE (TBL_Issues.Responsible=[Forms]![Main]![cboResponsible]
OR [Forms]![Main]![cboResponsible]="(All)")
AND (TBL_Issues.Status=[Forms]![Main]![cboStatus]
OR [Forms]![Main]![cboStatus])="(All)")
AND (TBL_Issues.Learner=[Forms]![Main]![cboLearner]
OR [Forms]![Main]![cboLearner])="(All)")
ORDER BY TBL_Issues.IssueNumber

You used two separate criteria rows to build your query, didn't you? What
you have to realize using the query grid is that it ANDs together all
criteria on one row, and ORs rows of criteria together.
--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

oops last one was wrong code, here it is;


SELECT TBL_Issues.Responsible, TBL_Issues.Status, TBL_Issues.Programme,
TBL_Issues.IssueNumber, TBL_Issues.Issue, TBL_Issues.Recommendation,
TBL_Issues.DateRaised, TBL_Issues.DateCompleted, TBL_Issues.Comments
FROM TBL_Issues
WHERE (((TBL_Issues.Responsible)=[Forms]![Main]![cboResponsible]) AND
((TBL_Issues.Status)=[Forms]![Main]![cboStatus]) AND
((TBL_Issues.Learner)=[Forms]![Main]![cboLearner])) OR
((([Forms]![Main]![cboResponsible])="(All)") AND
(([Forms]![Main]![cboStatus])="(All)") AND
(([Forms]![Main]![cboLearner])="(All)"))
ORDER BY TBL_Issues.IssueNumber;

SELECT Learner, Learner FROM TBL_Issues
UNION SELECT "(All)" AS Tom, Null AS M FROM TBL_Issues;

Sounds as though you're working with the query-building grid.

For something like this, it's much better to work directly with the SQL.
WIth the query open, select SQL View from the View menu.

Copy and paste your SQL below, and I'll take a look at it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Right I have got it now working partly;
I changed the query code to look like this for each:

Criteria: [Forms]![Main]![cboResponsible]
OR: [Forms]![Main]![cboResponsible]="(All)"

Which was weird cause that didn't work first of all.

But now I have the probloem that it only displays All when I select

ALL

on
all 3 comboboxes.
If I decide to select Learners = ALL, Named Responsible and Status = Open,
it does not display
I can't seem to pinpoint

Thanks.
Neil

If all you want is the individual learners, then you probably want

SELECT Learner FROM TBL_Issues
UNION
SELECT "(All)" FROM TBL_Issues

To get All in 3 separate comboboxes, you'd have to do the same

sort

of

Forms!MyForm!cboLearner

= combobox
is Null
as

 

[Neil M]

yes thats correct and thanks.
although it now states i have an extra ) in my query???

I have looked ta it but can't trace it?

You need to change it to

SELECT TBL_Issues.Responsible, TBL_Issues.Status, TBL_Issues.Programme,
TBL_Issues.IssueNumber, TBL_Issues.Issue, TBL_Issues.Recommendation,
TBL_Issues.DateRaised, TBL_Issues.DateCompleted, TBL_Issues.Comments
FROM TBL_Issues
WHERE (TBL_Issues.Responsible=[Forms]![Main]![cboResponsible]
OR [Forms]![Main]![cboResponsible]="(All)")
AND (TBL_Issues.Status=[Forms]![Main]![cboStatus]
OR [Forms]![Main]![cboStatus])="(All)")
AND (TBL_Issues.Learner=[Forms]![Main]![cboLearner]
OR [Forms]![Main]![cboLearner])="(All)")
ORDER BY TBL_Issues.IssueNumber

You used two separate criteria rows to build your query, didn't you? What
you have to realize using the query grid is that it ANDs together all
criteria on one row, and ORs rows of criteria together.
--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

oops last one was wrong code, here it is;


SELECT TBL_Issues.Responsible, TBL_Issues.Status, TBL_Issues.Programme,
TBL_Issues.IssueNumber, TBL_Issues.Issue, TBL_Issues.Recommendation,
TBL_Issues.DateRaised, TBL_Issues.DateCompleted, TBL_Issues.Comments
FROM TBL_Issues
WHERE (((TBL_Issues.Responsible)=[Forms]![Main]![cboResponsible]) AND
((TBL_Issues.Status)=[Forms]![Main]![cboStatus]) AND
((TBL_Issues.Learner)=[Forms]![Main]![cboLearner])) OR
((([Forms]![Main]![cboResponsible])="(All)") AND
(([Forms]![Main]![cboStatus])="(All)") AND
(([Forms]![Main]![cboLearner])="(All)"))
ORDER BY TBL_Issues.IssueNumber;

SELECT Learner, Learner FROM TBL_Issues
UNION SELECT "(All)" AS Tom, Null AS M FROM TBL_Issues;



Sounds as though you're working with the query-building grid.

For something like this, it's much better to work directly with the SQL.
WIth the query open, select SQL View from the View menu.

Copy and paste your SQL below, and I'll take a look at it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Right I have got it now working partly;
I changed the query code to look like this for each:

Criteria: [Forms]![Main]![cboResponsible]
OR: [Forms]![Main]![cboResponsible]="(All)"

Which was weird cause that didn't work first of all.

But now I have the probloem that it only displays All when I

select
ALL

on
all 3 comboboxes.
If I decide to select Learners = ALL, Named Responsible and Status =
Open,
it does not display
I can't seem to pinpoint

Thanks.
Neil

If all you want is the individual learners, then you probably want

SELECT Learner FROM TBL_Issues
UNION
SELECT "(All)" FROM TBL_Issues

To get All in 3 separate comboboxes, you'd have to do the same

sort

of
thing
to each of them.

Note that putting (All) in the combobox alone isn't enough to get
everything
selected for your report, though. Assuming that your query is pointing
to
those 3 comboboxes, so that you can limit what's selected when you
want
to,
you'll have to modify your query to handle (All).

If your query has something like:

WHERE Learner = Forms!MyForm!cboLearner

in it, you're going to need something like:

WHERE (Learner = Forms!MyForm!cboLearner OR

Forms!MyForm!cboLearner

=
'(All)')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


right thanks. i think i'm beginning to understand although I have
tried
all
the code below
and get the same results...
it shows ALL on the top of the list, duplicates the names

(each
name

has
a
different issue number associated with it - how can i get rid of
duplicate
names?) but when i run the query that links the comboboxes (Learner,
Responsible and Status) it shows no records?

I think it's the method I'm using rather than the coding - but how
would
I
get the 3 comboboxes (which are linked to one query linked to a
report)
to
display my report criteria including ALL.
I need to put ALL on all 3 comboboxes so that I can print out a
report
of
everything at once also.

Thanks for your help thus far..
Least I'm learning as I go!

message
That's very odd. The fact that you're using UNION, rather than
UNION
ALL,
means that all duplicates should be eliminated.

Of course, if a Learner can have multiple IssueNumbers associated,
then
you
will get apparent duplicates, as it will only eliminate duplicate
combinations of Learner and IssueNumber: the same Learner will
appear
multiple times, once for each IssueNumber associated with that
Learner.

As to why ALL isn't at the top, if what appears in your combobox
is
the
Learners, you've put (All) in the wrong place in your second
SELECT
query:
you need it to be the same column as Learner:

SELECT Learner, IssueNumber FROM TBL_Issues
UNION
SELECT "(All)" as Learner, Null As IssueNumber FROM TBL_Issues

For what it's worth, it's actually not critical that you

alias
the

fields
in
the second or subsequent SELECTs in a UNION query: the field names
from
the
first SELECT are what's picked as the query's fields, and the
position
of
the fields in the subsequent SELECTs is all that matters. In other
words,
you could have used

SELECT Learner, IssueNumber FROM TBL_Issues
UNION
SELECT "(All)", Null FROM TBL_Issues

or even

SELECT Learner, IssueNumber FROM TBL_Issues
UNION
SELECT "(All)" AS Tom, Null AS M FROM TBL_Issues


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Currently my coding looks like this (Union Query);

SELECT Learner, IssueNumber FROM TBL_Issues UNION (SELECT Null
as
Learner,
"(All)" As IssueNumber FROM TBL_Issues);

But that just shows me a list of all learners (duplicates as
well)
and
no
ALL at the top.



I've looked on lots of sites for this and been directed to
others
but
I
still do not understand how this works?

Can anyone explain how to do it it lame man's terms please?

Thanks.

 

[Douglas J Steele]

Sorry: my fault. Actually, there were 2 extra ones. (I don't use nearly as
many parentheses as Access does, and I forgot to clean up the original SQL!)

SELECT TBL_Issues.Responsible, TBL_Issues.Status, TBL_Issues.Programme,
TBL_Issues.IssueNumber, TBL_Issues.Issue, TBL_Issues.Recommendation,
TBL_Issues.DateRaised, TBL_Issues.DateCompleted, TBL_Issues.Comments
FROM TBL_Issues
WHERE (TBL_Issues.Responsible=[Forms]![Main]![cboResponsible]
OR [Forms]![Main]![cboResponsible]="(All)")
AND (TBL_Issues.Status=[Forms]![Main]![cboStatus]
OR [Forms]![Main]![cboStatus]="(All)")
AND (TBL_Issues.Learner=[Forms]![Main]![cboLearner]
OR [Forms]![Main]![cboLearner]="(All)")
ORDER BY TBL_Issues.IssueNumber

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

yes thats correct and thanks.
although it now states i have an extra ) in my query???

I have looked ta it but can't trace it?

You need to change it to

SELECT TBL_Issues.Responsible, TBL_Issues.Status, TBL_Issues.Programme,
TBL_Issues.IssueNumber, TBL_Issues.Issue, TBL_Issues.Recommendation,
TBL_Issues.DateRaised, TBL_Issues.DateCompleted, TBL_Issues.Comments
FROM TBL_Issues
WHERE (TBL_Issues.Responsible=[Forms]![Main]![cboResponsible]
OR [Forms]![Main]![cboResponsible]="(All)")
AND (TBL_Issues.Status=[Forms]![Main]![cboStatus]
OR [Forms]![Main]![cboStatus])="(All)")
AND (TBL_Issues.Learner=[Forms]![Main]![cboLearner]
OR [Forms]![Main]![cboLearner])="(All)")
ORDER BY TBL_Issues.IssueNumber

You used two separate criteria rows to build your query, didn't you? What
you have to realize using the query grid is that it ANDs together all
criteria on one row, and ORs rows of criteria together.
--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

oops last one was wrong code, here it is;


SELECT TBL_Issues.Responsible, TBL_Issues.Status, TBL_Issues.Programme,
TBL_Issues.IssueNumber, TBL_Issues.Issue, TBL_Issues.Recommendation,
TBL_Issues.DateRaised, TBL_Issues.DateCompleted, TBL_Issues.Comments
FROM TBL_Issues
WHERE (((TBL_Issues.Responsible)=[Forms]![Main]![cboResponsible]) AND
((TBL_Issues.Status)=[Forms]![Main]![cboStatus]) AND
((TBL_Issues.Learner)=[Forms]![Main]![cboLearner])) OR
((([Forms]![Main]![cboResponsible])="(All)") AND
(([Forms]![Main]![cboStatus])="(All)") AND
(([Forms]![Main]![cboLearner])="(All)"))
ORDER BY TBL_Issues.IssueNumber;



SELECT Learner, Learner FROM TBL_Issues
UNION SELECT "(All)" AS Tom, Null AS M FROM TBL_Issues;



Sounds as though you're working with the query-building grid.

For something like this, it's much better to work directly with

the
SQL.

WIth the query open, select SQL View from the View menu.

Copy and paste your SQL below, and I'll take a look at it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Right I have got it now working partly;
I changed the query code to look like this for each:

Criteria: [Forms]![Main]![cboResponsible]
OR: [Forms]![Main]![cboResponsible]="(All)"

Which was weird cause that didn't work first of all.

But now I have the probloem that it only displays All when I select
ALL
on
all 3 comboboxes.
If I decide to select Learners = ALL, Named Responsible and

Status

= I
have

rid

of but
how

to

a

out

a (SELECT
Null

(duplicates

as

directed

to

 

[Neil M]

sweet thanks it is now working perfectly.
thanks for all your help. i have learn't a lot over the last few days..

many thanks.

neil

Sorry: my fault. Actually, there were 2 extra ones. (I don't use nearly as
many parentheses as Access does, and I forgot to clean up the original SQL!)

SELECT TBL_Issues.Responsible, TBL_Issues.Status, TBL_Issues.Programme,
TBL_Issues.IssueNumber, TBL_Issues.Issue, TBL_Issues.Recommendation,
TBL_Issues.DateRaised, TBL_Issues.DateCompleted, TBL_Issues.Comments
FROM TBL_Issues
WHERE (TBL_Issues.Responsible=[Forms]![Main]![cboResponsible]
OR [Forms]![Main]![cboResponsible]="(All)")
AND (TBL_Issues.Status=[Forms]![Main]![cboStatus]
OR [Forms]![Main]![cboStatus]="(All)")
AND (TBL_Issues.Learner=[Forms]![Main]![cboLearner]
OR [Forms]![Main]![cboLearner]="(All)")
ORDER BY TBL_Issues.IssueNumber

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

yes thats correct and thanks.
although it now states i have an extra ) in my query???

I have looked ta it but can't trace it?

You need to change it to

SELECT TBL_Issues.Responsible, TBL_Issues.Status, TBL_Issues.Programme,
TBL_Issues.IssueNumber, TBL_Issues.Issue, TBL_Issues.Recommendation,
TBL_Issues.DateRaised, TBL_Issues.DateCompleted, TBL_Issues.Comments
FROM TBL_Issues
WHERE (TBL_Issues.Responsible=[Forms]![Main]![cboResponsible]
OR [Forms]![Main]![cboResponsible]="(All)")
AND (TBL_Issues.Status=[Forms]![Main]![cboStatus]
OR [Forms]![Main]![cboStatus])="(All)")
AND (TBL_Issues.Learner=[Forms]![Main]![cboLearner]
OR [Forms]![Main]![cboLearner])="(All)")
ORDER BY TBL_Issues.IssueNumber

You used two separate criteria rows to build your query, didn't you? What
you have to realize using the query grid is that it ANDs together all
criteria on one row, and ORs rows of criteria together.
--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


oops last one was wrong code, here it is;


SELECT TBL_Issues.Responsible, TBL_Issues.Status, TBL_Issues.Programme,
TBL_Issues.IssueNumber, TBL_Issues.Issue, TBL_Issues.Recommendation,
TBL_Issues.DateRaised, TBL_Issues.DateCompleted, TBL_Issues.Comments
FROM TBL_Issues
WHERE (((TBL_Issues.Responsible)=[Forms]![Main]![cboResponsible]) AND
((TBL_Issues.Status)=[Forms]![Main]![cboStatus]) AND
((TBL_Issues.Learner)=[Forms]![Main]![cboLearner])) OR
((([Forms]![Main]![cboResponsible])="(All)") AND
(([Forms]![Main]![cboStatus])="(All)") AND
(([Forms]![Main]![cboLearner])="(All)"))
ORDER BY TBL_Issues.IssueNumber;



SELECT Learner, Learner FROM TBL_Issues
UNION SELECT "(All)" AS Tom, Null AS M FROM TBL_Issues;



Sounds as though you're working with the query-building grid.

For something like this, it's much better to work directly with the
SQL.
WIth the query open, select SQL View from the View menu.

Copy and paste your SQL below, and I'll take a look at it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Right I have got it now working partly;
I changed the query code to look like this for each:

Criteria: [Forms]![Main]![cboResponsible]
OR: [Forms]![Main]![cboResponsible]="(All)"

Which was weird cause that didn't work first of all.

But now I have the probloem that it only displays All when I select
ALL
on
all 3 comboboxes.
If I decide to select Learners = ALL, Named Responsible and

Status

=
Open,
it does not display
I can't seem to pinpoint

Thanks.
Neil

message
If all you want is the individual learners, then you

probably
want

SELECT Learner FROM TBL_Issues
UNION
SELECT "(All)" FROM TBL_Issues

To get All in 3 separate comboboxes, you'd have to do the same
sort
of
thing
to each of them.

Note that putting (All) in the combobox alone isn't enough

to
get

everything
selected for your report, though. Assuming that your query is
pointing
to
those 3 comboboxes, so that you can limit what's selected wh

en
you

want
to,
you'll have to modify your query to handle (All).

If your query has something like:

WHERE Learner = Forms!MyForm!cboLearner

in it, you're going to need something like:

WHERE (Learner = Forms!MyForm!cboLearner OR
Forms!MyForm!cboLearner
=
'(All)')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


right thanks. i think i'm beginning to understand

although

I rid to out wrote
in rather
than Learner
will with
that and
the

matters.

In (duplicates directed