I have a Variable Input where a customer enters a list. i.e.: Software, Engineer, IT, CISO, CEO.
I am using that list to generate some Set statements for data filtering.
I can do this: Chr(39) & Replace('$(vTitle)',', ', Chr(39) & ', ' & Chr(39)) & Chr(39)
to generate a list like 'Software', 'Engineer', 'IT', 'CISO', 'CEO'. - This list in a set statement does an explicit case-sensitive match.
Or I can do this: '"*' & Replace('$(vTitle)', ', ', '*", "*') & '*"'
to generate a list like "*Software*", "*Engineer*", "*IT*", "*CISO*", "*CEO*" - This list in a set statement does a case-insensitive match of the word anywhere in a job title.
What I want is the ability to insert some logic that says if an item is 4 letters or less, then do the first one, but if it is longer than that, do the second. So, it would generate a list like: "*Software*", "*Engineer*", 'IT', 'CISO', 'CEO'.
Does anyone know how to do that? The user needs to be able to enter anything, so perhaps some combination of an IF() statement and the ValueList() function to evaluate each item in the list uniquely? I have not been able to find anything that works yet.
