Using string as if statement

[bradyboyy88]

So I draw a mistype error for this code when I try to build a if statement from a string. Is this even possible?

Dim filter as string

filter= "variable=5"

if filter then

blah

end if

 

[bradyboyy88]

The EVALUATE basically expects a phrase formatted as a sheet function, and you're giving it VBA syntax. Try:

If Evaluate("AND(5=5,6=6)") Then

or

If Evaluate("(5=5)*(6=6)") = 1 Then

Can you use vba objects in evaluate then? For instance OR(recordset.fields("blah").value=10,recordset.fields("blah").value=11).

Also, I know you said there might be a better idea to do what I am trying to do so here is exactly what I need to do. I have 8 filter buttons on my userform. and when you click one a combo box becomes visisble and popus up next to the filter button and it is filled with data from a recordset for that particular field. Like one of the columns is states, the next column is starting date, , etc. So if you click the state filter all 50 states come up and I use a routine that makes sure the list is unique so its all 50 states or which states are in the dataset to be exact and you can select which items you want to filter on. So then the next filter button it needs to be dependent on the last one that was pressed so it only fills on data for those selected states. So in ado filters you cannot keep doing cumulative filters on a recordset. So my thought was to keep building a string with OR statements for each of the selected items then combine then with AND statements from each filter. The hard part is trying to make each filter dependent on the last and then the ability to unfilter (select all) similar to excel. Honestly if I can figure out a method of imitating the excel filter but instead of the data being in the excel sheet fields they are in a ado recordset then my goal is accomplished. I just cant think of an idea creative enough tto do this.

One idea i had was to cumulativelybuild this OR statements using an array and when one filter is selected it adds it in the first position of the array. Then the second filter added is added to the second position of the array with the first set of selected items AND the second set of selected items. That way if they go back to the first filter they can still see the items selected originally . I know this is probably confusing but just think about excel filtering is my only way to say what I am doing lol. Any creative ideas of how i can accomplish my goal?

 

[Eric W]

You can't use a VBA object in EVALUATE, but you can use properties of the object, such as Value, or Name, as long as it evaluates to a text value.

As far as your overall goal, I kind of get the gist of what you're trying to do, and I do think there should be a way to accomplish what you want. However, there are too many details for me to handle at a distance. I don't think I'd be able to figure something out without more time and more "hands on". But I wish you well! Keep plugging at it, and I'm sure you'll get it! Good luck!

 

[bradyboyy88]

You can't use a VBA object in EVALUATE, but you can use properties of the object, such as Value, or Name, as long as it evaluates to a text value.

As far as your overall goal, I kind of get the gist of what you're trying to do, and I do think there should be a way to accomplish what you want. However, there are too many details for me to handle at a distance. I don't think I'd be able to figure something out without more time and more "hands on". But I wish you well! Keep plugging at it, and I'm sure you'll get it! Good luck!

Yea I ended up trying to be a bit smarter about this. I was able to recreate the dynamic filtering on the userform like excel but it took use of arrays and having two recordsets. To get around de morgans law issue with filter I create a recursive filter function that recreates new recordsets to add additional filters which creates an AND by nature. Its blazing fast since its a recordset so all is well now. Thanks so much for the help.