autocomplete where clause CASE statement. Is it possible to include?

Justin Kraemer

August 15, 2017 14:45

I have a form with an autocompletebox that I reference in the where clause as:

 

UPPER(CivicAddress) Like UPPER('{0}%') AND Municipality IN ( '{1:ComboBox1}' )

 

This works beautifully so long as the user chooses a municipality in the combobox. When they do not, the default combobox value is All of Bruce County, and while the query will execute just fine, the autocomplete shows nothing. This I'd like to change.

 

What I hope to be able to do is use a SQL case statement to define the Where clause without the 

 

AND Municipality IN ( '{1:ComboBox1}' )

 

portion whenever the combobox value is the default. Is there any way to achieve this, and if so, how? Is there an alternative way?

 

Thanks for any suggestions you might have,

Justin

0

• 

  Justin Kraemer

  August 15, 2017 14:51

  Just to clarify one thing, the reason I have an IN statement in place of  what could be (and formerly was) a simple = {1:ComboBox1} is I had hoped to replace the default combo box value with a list of all municipality names, but that failed because of the necessary quotes around {1:ComboBox1}. 

  0
• 

  Justin Kraemer

  August 15, 2017 15:02

  Figured it out. I simply changed the 

   

  AND Municipality IN ( '{1:ComboBox1}' )

   

  portion into

   

  AND Municipality LIKE '{1:ComboBox1}'

   

  and then set the combobox value for All of Bruce County to % 

   

  So now it works, though I'm still curious if CASE statements are supported in an autocomplete box Where clause. Can someone say?

  0

Please sign in to leave a comment.