Parameter Query

  • Thread starter Thread starter Fie
  • Start date Start date
F

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
 
SELECT tblAddData.Date, tblAddData.TextilesSite,
tblAddData.TextilesWeight, tblAddData.TextilesGroup,
Format([Date],"mmmm") AS Month
FROM tblAddData
WHERE (((tblAddData.Date) Between
[Forms]![frmWhatDates14]![txtStartDate] And
[Forms]![frmWhatDates14]![txtEndDate]) AND
((tblAddData.TextilesWeight)>0) AND ((tblAddData.TextilesGroup) Is Null
Or (tblAddData.TextilesGroup)=[Forms]![frmWhatDates14]![cboGroup]));
 
Add 'collection type' of "All" to the combo box cboCollectionType.

(tblAddData.TextilesGroup) Like
IIF([Forms]![frmWhatDates14]![cboGroup]="All", "*",
[Forms]![frmWhatDates14]![cboGroup])));

If your combo box cboCollectionType is using two columns like --
1 Green
2 Special
3 Paper
4 Brown

Then add ---
99 All

and use --
(tblAddData.TextilesGroup) Like IIF([Forms]![frmWhatDates14]![cboGroup]=99,
"*", [Forms]![frmWhatDates14]![cboGroup])));
 
Back
Top