Using Filter Property with multivalued fields

[cmpd]

Hi all,

I'm having a bit of trouble so decided to join up!

I have a subform query which includes EventName, Date, and Tags. Tags is a multivalued field.

What I need to do is allow people to filter this subform using the inbuilt filter, and then use the filtered data to populate a graph.

I am doing this by taking the Filter property from the form and then using VBA to build an SQL statement with the right data, and using that on the graph. It works fine when people filter on the EventName and Date fields.

However, when someone filters the Tags field, it fails. The filter property gets set to something along the lines of

([Lookup_Tags].[TagName]="whatevertagtheypick")

This syntax seems not to be supported when I put it into the SQL statement.

Any ideas on how to get it to work? If there's a completely different approach which is better I'm open to it!

 

[nathansav]

When you are modifying for SQL you will need to put it like this

Where FIELD like "*WHATEVERTHEYWANT*"

You can pull this out, by using instr and instrrev to search for the "'s and then mid to get the text inbetween them.