Skip to content
Advertisement

VBA Query based on multiple “multiple select list boxes” in Access when not selecting an item from one of the multiple select boxes

I have the following vba that creates a query in a test Access database. I have two multiple select list boxes. The issue is, i want to be able to select multiple items from “Me![State]” and none from “Me![Animal]” and be able to run the query. However, this is not possible as the query language is not set up to handle that. It makes me select something from “Me![Animal]”.

How do i revise the vba below to allow me to query on both multiple selection list boxes if one of the multiple list boxes does not have anything selected or if both have selections in them?

Advertisement

Answer

EDIT – Fix comparison as per comment

You can do this with a simple check of your Criteria vaiables.

You already do the the length check – just use it later on when you build the dynamic SQL.

I added a strSQL variable to make it easier to track what’s happening. And adjusted the error message to allow one or other criteria being empty

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement