Parameter query show All Values on Report

[snoopy92211]

I'm having a devil of a time getting this report to load. My report is based on a parameter query.

My combobox serves as a parameter. The combobox is based on this query:

SELECT AItemType.ItemTypeID, AItemType.ItemType FROM Aitemtype order by Aitemtype.itemtype;

There are 4 choices the user can select. Car, Truck, Bike, Plane. From their choice, the user selects a preview report command button and the report appears with the appropriate information.

The comboboxes work fine when I select an individual item in the combobox. I want the user to be able to select all, and instead of the report populating with just one choice, the entire selection will appear on the report. So the report will populate with data from car, truck, bike, and plane.

Here is my underlying report query. And how would I show on the report that all items have been selected? Right now, I just have ItemType as a field, but that doesn't work.

SELECT AItem.ItemType, AItem.ItemTypeII, AItem.ItemNumber, AItem.ItemDescription, AItem.Company, AItem.CompletionPercentage, AItem.ReceviedFromA, AItem.OriginalDueDate, AItem.RevisedDueDate, AItem.DateSubmitted, AItem.HoursToComplete, AItem.Responsible, AItem.Primary, AItem.PrimaryHours, AItem.Support, AItem.SupportHours, AItem.Review, AItem.ReviewHours, AItem.DueToReviewer, AItem.Finalized, AItem.TPorNA, AItem.IncomeCredit, AItem.NoResponse, 
FROM AItem
WHERE (((AItem.ItemType)=IIf(IsNull([forms]![reports]![itemtype]),"*",[forms]![reports]![itemtype])) AND ((AItem.ItemNumber) Not Like ("*F*")) AND ((AItem.Name)=IIf(IsNull([forms]![reports]![auditname]),"*",[forms]![reports]![auditname])));

 

[WayneRyan]

Snoopy,

Extra comma after NoResponse.

[ItemType] will never be = "*". To use the Wildcard, you need Like "*".

"Name" is a reserved word, you should change it to something else.

Something like this should work:

SELECT ItemType,             ItemTypeII,      ItemNumber,      ItemDescription,  Company, 
       CompletionPercentage, ReceviedFromA,   OriginalDueDate, RevisedDueDate,   DateSubmitted,    
       HoursToComplete,      Responsible,     Primary,         PrimaryHours,     Support, 
       SupportHours,         Review,          ReviewHours,     DueToReviewer,    Finalized, 
       TPorNA,               IncomeCredit,    NoResponse  <-- Removed Comma !!!
FROM   AItem
WHERE (ItemType [forms]![reports]![itemtype] Or   <-- Include the ItemType if it matches the combo
       [forms]![reports]![itemtype] = "ALL") AND  <-- Or if the combo has "ALL"
       ItemNumber Not Like "*F*" AND 
      (Name = [forms]![reports]![auditname] Or    <-- Include the Auditname if it matches [AuditName]
       [forms]![reports]![auditname] Is Null);    <-- Or if [AuditName] Is Null

Wayne