providing an 'any' option within a combo box

[alex]

hi

within a combo box how do i include an 'any' option so that all the options
within the combo box are valid

for example on a form with multiple combo boxes some options may be selected
but others may not be specified and so an 'any' selection will not rule out
any records within that particular field

thanks

 

[Klatuu]

Explained here:

http://www.mvps.org/access/forms/frm0043.htm

 

[Douglas J. Steele]

Just to tag along on Dave's response.

Dave's pointed you to a resource that will let you add the "Any" option to
your combo box. However, if you're trying to use that combo box as a
criteria for a query, you'll need to ensure that you handle that new option
correctly.

Assuming you've got Forms!NameOfForm!NameOfCombo as a criteria in your
query, you'll need to change that to

Forms!NameOfForm!NameOfCombo Or (Forms!NameOfForm!NameOfCombo = "any")

 

[alex]

this doesnt seem to work
just to make sure we arent crossing wires

assuming i want to run a query to find a certain car
a combo box with the options red, blue, yellow and green is available
picking red would result in all records with the colour red being found
but if i do not care what colour the car was i could select and "all" option
this would result in all records being shown irrespective of the colour

i am sure you knew i meant this anyway, its just i am new to all this
thanks

 

[Douglas J. Steele]

Yes, what I proposed was intended to accomplish what you're trying to do.

What are you adding to your combo box: "all" or "any"? (both have been used
in this thread)

Let's see the code you're using to add the value to your combo.

As well, let's see the SQL for your query.

 

[alex]

my rowsource for my combo box(combo6) is

"(Any)";"Infrastructure";"Civil Engineering";"Building";"Minor Works"


SQL for my query

SELECT [Project Tbl].[Project ID], [Project Tbl].[Project Name], [Company
Tbl].[Company Name], [Project Tbl].Division, [Project Tbl].Town, [Project
Tbl].Region, [Project Tbl].[Start Date], [Project Tbl].[Duration (Mths)],
[Project Tbl].[Cost (£)], [Project Tbl].Ongoing
FROM [Company Tbl] INNER JOIN [Project Tbl] ON [Company Tbl].[Company ID] =
[Project Tbl].[Company ID]
WHERE ((([Project Tbl].Division)=[Forms]![Project Search Frm]![combo6] Or
([Project Tbl].Division)=([Forms]![Project Search Frm]![combo6="Any"])));

 

[Douglas J. Steele]

Because you've put parentheses around the word Any in your combo box, you
need to put it in the Where clause as well.

WHERE ([Project Tbl].Division=[Forms]![Project Search Frm]![combo6]) Or
([Forms]![Project Search Frm]![combo6]="(Any)")

As well, what was after the Or in the Where clause is invalid. Probably
easiest to correct the SQL as above, rather than working with the grid.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

my rowsource for my combo box(combo6) is

"(Any)";"Infrastructure";"Civil Engineering";"Building";"Minor Works"


SQL for my query

SELECT [Project Tbl].[Project ID], [Project Tbl].[Project Name], [Company
Tbl].[Company Name], [Project Tbl].Division, [Project Tbl].Town, [Project
Tbl].Region, [Project Tbl].[Start Date], [Project Tbl].[Duration (Mths)],
[Project Tbl].[Cost (£)], [Project Tbl].Ongoing
FROM [Company Tbl] INNER JOIN [Project Tbl] ON [Company Tbl].[Company ID]
=
[Project Tbl].[Company ID]
WHERE ((([Project Tbl].Division)=[Forms]![Project Search Frm]![combo6] Or
([Project Tbl].Division)=([Forms]![Project Search Frm]![combo6="Any"])));

Yes, what I proposed was intended to accomplish what you're trying to do.

What are you adding to your combo box: "all" or "any"? (both have been
used
in this thread)

Let's see the code you're using to add the value to your combo.

As well, let's see the SQL for your query.

 

[alex]

cheers thats brilliant

Because you've put parentheses around the word Any in your combo box, you
need to put it in the Where clause as well.

WHERE ([Project Tbl].Division=[Forms]![Project Search Frm]![combo6]) Or
([Forms]![Project Search Frm]![combo6]="(Any)")

As well, what was after the Or in the Where clause is invalid. Probably
easiest to correct the SQL as above, rather than working with the grid.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

my rowsource for my combo box(combo6) is

"(Any)";"Infrastructure";"Civil Engineering";"Building";"Minor Works"


SQL for my query

SELECT [Project Tbl].[Project ID], [Project Tbl].[Project Name], [Company
Tbl].[Company Name], [Project Tbl].Division, [Project Tbl].Town, [Project
Tbl].Region, [Project Tbl].[Start Date], [Project Tbl].[Duration (Mths)],
[Project Tbl].[Cost (£)], [Project Tbl].Ongoing
FROM [Company Tbl] INNER JOIN [Project Tbl] ON [Company Tbl].[Company ID]
=
[Project Tbl].[Company ID]
WHERE ((([Project Tbl].Division)=[Forms]![Project Search Frm]![combo6] Or
([Project Tbl].Division)=([Forms]![Project Search Frm]![combo6="Any"])));

Yes, what I proposed was intended to accomplish what you're trying to do.

What are you adding to your combo box: "all" or "any"? (both have been
used
in this thread)

Let's see the code you're using to add the value to your combo.

As well, let's see the SQL for your query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


this doesnt seem to work
just to make sure we arent crossing wires

assuming i want to run a query to find a certain car
a combo box with the options red, blue, yellow and green is available
picking red would result in all records with the colour red being found
but if i do not care what colour the car was i could select and "all"
option
this would result in all records being shown irrespective of the colour

i am sure you knew i meant this anyway, its just i am new to all this
thanks

:

Just to tag along on Dave's response.

Dave's pointed you to a resource that will let you add the "Any"
option
to
your combo box. However, if you're trying to use that combo box as a
criteria for a query, you'll need to ensure that you handle that new
option
correctly.

Assuming you've got Forms!NameOfForm!NameOfCombo as a criteria in your
query, you'll need to change that to

Forms!NameOfForm!NameOfCombo Or (Forms!NameOfForm!NameOfCombo = "any")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Explained here:

http://www.mvps.org/access/forms/frm0043.htm
--
Dave Hargis, Microsoft Access MVP


:

hi

within a combo box how do i include an 'any' option so that all the
options
within the combo box are valid

for example on a form with multiple combo boxes some options may be
selected
but others may not be specified and so an 'any' selection will not
rule
out
any records within that particular field

thanks