Using "All" in a Parameter Query

[Hrithika]

I know how to display all the records if no parameters are selected on the form. But my user is adamant about having the "All" option in dropdown menu and he would like to see all the records if that option is selected. Currently, the query would display all the records if no selection is made for the Controls CmbLocation or CmbType. The options in CmbLocations are names of 9 different locations. The user would like the 10th option "All". I don't know how to show all records if the option "All" is selected.

Any help is appreciated. Thanks in advance.

Given below is my query

SELECT tblLeaveRequest.Location, tblLeaveRequest.Type, tblLeaveRequest.StartDate, tblLeaveRequest.EndDate, iif([Forms]![Analysis].[StartDate]>[StartDate],[Forms]![Analysis].[StartDate],[StartDate]) as UsedStartdate,
iif([Forms]![Analysis].[EndDate]<[EndDate],[Forms]![Analysis].[EndDate],[EndDate]) as UsedEnddate,
funWorkDaysDifference(iif([Forms]![Analysis].[StartDate]>[StartDate],[Forms]![Analysis].[StartDate],[StartDate]),
iif([Forms]![Analysis].[EndDate]<[EndDate],[Forms]![Analysis].[EndDate],[EndDate]))
AS WorkDaysDifference
FROM tblLeaveRequest
WHERE ((([Forms]![Analysis].[cmbLocation]) Is Null) or (tblLeaveRequest.Location)=[Forms]![Analysis].[cmbLocation])
AND ((([Forms]![Analysis].[cmbType]) is null) or (tblLeaveRequest.Type)=[Forms]![Analysis].[cmbType])
AND (((tblLeaveRequest.EndDate)>=[Forms]![Analysis].[StartDate]) AND ((tblLeaveRequest.StartDate)<=[Forms]![Analysis].[EndDate]))
AND (tblLeaveRequest.Approved = "YES")

 

[Alansidman]

I suppose that in your table that you use for the lookups, you could create a third field that is the one that is displayed. In that field list all the options including one that says "ALL." In the next field, list all the options available and in the record for all, put the Wildcard "*". Create a hidden text box on your form that will be populated with the values from the second field, ie. the one holding the "*'. Use this field as your critieria for the query. Your user will never know the difference. A bit of work, but will satisfy his needs.

Use the autofill tutorial here for the text box.

http://www.baldyweb.com/Autofill.htm

Good luck

 

[Brianwarnock]

If he can get ALL into the list then all he needs to do is change the Is Null to = "ALL"

Brian

 

[MSAccessRookie]

We might not need to know the details of your Query in order to add an item to a ComboBox that is based on it, only the structure. As an example, if your Query returns a Single Text Item (like a name), then the code below will give you an additional Line at the end.

ComboBox.Items.Add("Display All")

You can then use the ON Change Event for the ComboBox to display what the user is asking for by using your code that displays all items if the user selects "Display All"

Would this be of any use to you?

-- Rookie

 

[Hrithika]

If he can get ALL into the list then all he needs to do is change the Is Null to = "ALL"

Brian

After trying more complex tricks, I realized this one is the easiest. For some reason it did not work the first time and I discarded this solution. But this is the simplest one.

This is how my query looks like now. The change is highlighted in red.

SELECT tblLeaveRequest.Location, tblLeaveRequest.Type, tblLeaveRequest.StartDate, tblLeaveRequest.EndDate, iif([Forms]![Analysis].[StartDate]>[StartDate],[Forms]![Analysis].[StartDate],[StartDate]) as UsedStartdate,
iif([Forms]![Analysis].[EndDate]<[EndDate],[Forms]![Analysis].[EndDate],[EndDate]) as UsedEnddate,
funWorkDaysDifference(iif([Forms]![Analysis].[StartDate]>[StartDate],[Forms]![Analysis].[StartDate],[StartDate]),
iif([Forms]![Analysis].[EndDate]<[EndDate],[Forms]![Analysis].[EndDate],[EndDate]))
AS WorkDaysDifference
FROM tblLeaveRequest
WHERE ((([Forms]![Analysis].[cmbLocation]) ='All') or (tblLeaveRequest.Location)=[Forms]![Analysis].[cmbLocation])
AND ((([Forms]![Analysis].[cmbType]) is null) or (tblLeaveRequest.Type)=[Forms]![Analysis].[cmbType])
AND (((tblLeaveRequest.EndDate)>=[Forms]![Analysis].[StartDate]) AND ((tblLeaveRequest.StartDate)<=[Forms]![Analysis].[EndDate]))
AND (tblLeaveRequest.Approved = "YES")

 

[Hrithika]

We might not need to know the details of your Query in order to add an item to a ComboBox that is based on it, only the structure. As an example, if your Query returns a Single Text Item (like a name), then the code below will give you an additional Line at the end.

ComboBox.Items.Add("Display All")

You can then use the ON Change Event for the ComboBox to display what the user is asking for by using your code that displays all items if the user selects "Display All"

Would this be of any use to you?

-- Rookie

Thanks for the response. My query returns a set of multiples records. I guess you suggestion will not work that instance.

 

[Hrithika]

I suppose that in your table that you use for the lookups, you could create a third field that is the one that is displayed. In that field list all the options including one that says "ALL." In the next field, list all the options available and in the record for all, put the Wildcard "*". Create a hidden text box on your form that will be populated with the values from the second field, ie. the one holding the "*'. Use this field as your critieria for the query. Your user will never know the difference. A bit of work, but will satisfy his needs.

Use the autofill tutorial here for the text box.

http://www.baldyweb.com/Autofill.htm

Good luck

Thanks for your response. I am going to try this solution. Will get back to you if it doesn't work.

But before I try this more complex method, let me ask a simple simple question.

Currently the Where clause begins with

WHERE ((([Forms]![Analysis].[cmbLocation]) Is Null) or (tblLeaveRequest.Location)=[Forms]![Analysis].[cmbLocation])

Can it somehow be changed to work like this? I am getting an error message, but I would like to know if it is even feasible?

WHERE ((IIF([Forms]![Analysis].[cmbLocation]="All","")) or (tblLeaveRequest.Location)=[Forms]![Analysis].[cmbLocation])

I know I am lacking an argument in the IIF function but that is exactly what I want to achieve. If All is selected in the combo box, run the query as if the combobox is null.

Usually we would use nz function to replace null with something else. I want to do the opposite. Is there any function that would replace something with a null.

 

[Alansidman]

In the Where clause I would put,
WHERE SomeQueryField = Like "*" & [Forms]![Analysis].[cmbLocation] & "*"

 

[MSAccessRookie]

Thanks for the response. My query returns a set of multiples records. I guess you suggestion will not work that instance.

Please explain what you mean by multiple records. If you mean that there is more than a single Field in each row, then the Add feature can handle that. Look up the Syntax for details.