Forms, Parameters & so much confusion...

[Fie]

Hi,

I have created a form to supply parameters to a query, on it I have a
combo box cboCollectionType which lists all the collection types
(Special, Green, Cans & Glass) and also txtStartDate and txtEndDate.
This is great when the the user wants a report based on a specific
collection type between the two dates. How can I change it so that
there is an option avalible to the user to view the details for all
the collection types between the two dates.


Fiona

 

[Jeff Boyce]

Fiona

What happens now if you leave the combobox unselected?

Could you alter the query's parameter for the combobox field to something
like:
Is Null Or Forms!YourFormName!cboYourComboBoxControl

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Fie]

Hey,

When u leave it blank it does not display any info in the report.
I tried putting
Is Null Or [Forms]![frmWhatDates14]![cboGroup] and it just comes up
with a black report..

Fie

 

[Guest]

Here is a site that will show you how you can add a selection for "ALL" to
your combo.

The you can set your criteria up to handle either all or a specific selection:

LIKE (IIF([Forms]![MyForm]![MyCombo] = "ALL", "*",[Forms]![MyForm]![MyCombo])

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

 

[Fie]

Hey,

Iv added in LIKE (IIF([Forms]![MyForm]![MyCombo] = "ALL",
"*",[Forms]![MyForm]![MyCombo]) in to the query but when I run it.. I
type in All or just leave it black no results are displayed..

Fiona

 

[Guest]

When you type something in as a parameter, it is not the same as getting it
from the control. you can begin your testing without changing the combo by
running it from the form, and you should still get your selection. If that
works as expected, add the code that include the workd ALL in your combo
list, the select it and see what it returns.

 

[Fie]

When I run the report and select "All" from the combo box it just
displays values for one of the collection types. Mmm tad weirdo

 

[Guest]

I just set up a test and it is working fine for me. Here is what may be the
problem. In the code I suggested to you on the web site. The value is (All)
not All
So if you are filtering on "All" and getting one collection type, then the
value All must be in that collection type. Your query should be testing for
"(All)"

Here is the code from my test query:

Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])

Here is the rowsource for the comb:

SELECT ITM FROM tblITM UNION Select "(All)" as Bogus From tblITM ORDER BY
ITM;