<All> option on form to report

[snoopy92211]

Hello. I have a form with several comboboxes. This form will allow users to select whatever criteria they want and print a report using named criteria.

The reports are working fine until I decided that I wanted to add an <all> option in the combobox.

Here's my combobox, named ItemType. The rowsource type is table/query, the rowsource for the ItemType is

SELECT IRSItemType.ItemTypeID, IRSItemType.ItemType FROM IRSItemType UNION SELECT "*", "<All>" FROM irsitemtype order by irsitemtype.itemtype;

This code does not work. When I try to open this report, I get nothing. But -- when I select an individual item in my itemtype combobox, the report appears fine.

Please help!

 

[SeeThru]

What is the SQL for the report?

You proable need to change the WHERE to somthing like...

WHERE (IIf([myform]![cmbobox]="(All)",True,False))=false) AND [item]=myform!combobox) OR (IIf([myform]![cmdbox]="(All)",True,False))=true));

 

[snoopy92211]

ahh... The report's underlying data source is a query.

Here's a sample of the code:

SELECT IRSItem.ItemType, IRSItem.ItemTypeII, IRSItem.ItemNumber, IRSItem.ItemDescription, IRSItem.Company, IRSItem.CompletionPercentage,  IRSItem.Comments, IRSItem.Name
FROM IRSItem
[b]WHERE (((IRSItem.ItemType)=[forms]![reports]![itemtype]) [/b]AND ((IRSItem.ItemNumber) Not Like ("*F*")) AND ((IRSItem.Name)=[forms]![reports]![auditname])) OR (((IRSItem.Name)=[forms]![reports]![auditname]) AND (("forms]![reports]![itemtype]") Is Null)) OR (((IRSItem.ItemType)=[forms]![reports]![itemtype]) AND (([forms]![reports]![auditname]) Is Null)) OR ((("forms]![reports]![itemtype]") Is Null) AND (([forms]![reports]![auditname]) Is Null));

And in the report, I just have the textbox, [ItemType]. Are you saying that the bolded section in the code above needs to be the code you indicated in the query,
or
I have to change my report textbox? (Or both??)

Thanks!

 

[PHV]

WHERE ((IRSItem.ItemType=[forms]![reports]![itemtype] OR [forms]![reports]![itemtype]='*') AND ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snoopy92211]

But.... I changed that part of the query, and the report still isn't appearing.

The combobox, Itemtype has this are the RowSource:

SELECT IRSItemType.ItemTypeID, IRSItemType.ItemType FROM IRSItemType UNION SELECT "*", "<All>" FROM irsitemtype order by irsitemtype.itemtype;


When I changed the Report's underlying query to match PHV's comment, Nothing appeared.

What needs to be changed in the combobox?

 

[StarPassing]

The fields from the right side of the Union must equal the number of fields from the left side. Try this:

SELECT IRSItemType.ItemTypeID, IRSItemType.ItemType FROM IRSItemType UNION SELECT "*" as Choice1, "<All>" as Choice2 FROM irsitemtype order by irsitemtype.itemtype;

 

[PHV]

Something like this ?
WHERE (IRSItem.ItemType=[Forms]![reports]![itemtype] OR [Forms]![reports]![itemtype]='*')
AND IRSItem.ItemNumber Not Like '*F*'
AND (IRSItem.Name=[Forms]![reports]![auditname] OR [Forms]![reports]![auditname] Is Null);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886