Query WHERE Clause Not Working Properly

Permanently deleted user

5. November 2013 04:06

Hello Everyone,

I'm using the following WHERE Clause in my Query, however if I don't enter the full address in the search, it says it does not exist.:

"ADDRESS LIKE '%"& address &"%'"

I'm trying to make the query work so if I enter variations of an address such as 275 13th Street (real address 275 W 13th Street ), it will return with all possible addresses.

Instead it's saying that the address does not exist and only works when I enter the fully qualified address.

I've tried a variation of different Where clauses such as:  "ADDRESS like '" & address & "%'" and "ADDRESS like '%" & address & "'"  with no luck.

Any suggestions would be really appreciated!  I'm using Essentials Version 3.15.1 & Silverlight 1.10.1

Thank You!   -Laura-

 

 

0

• 

  Permanently deleted user

  5. November 2013 05:23

  This isn't my forte, but I use this to find anything that people type:

  "UPPER (ADDRESS) LIKE UPPER('" + "%" + address + "%" + "')"

  So for example, if the user types "ma" it will suggest "Main", "Irma", and "Armando".  However, I don't think this covers the instance where they miss the W in 275 W 13th Street.  It might be worth a shot, though.

  0
• 

  Permanently deleted user

  5. November 2013 05:31

  Thank You Stephani! With your suggestions I can enter 275 or 13th and it gives me a list of possible addresses, however it's still not accepting 275 13th St.

  I'm so close to figuring this out, It's probably something really simple that i'm overlooking! 

  Any more suggestions would defintely be helpful. Thank You!

  0
• 

  Permanently deleted user

  5. November 2013 05:42

  Great, I'm glad that got it part way there!  I don't know if you can add a wildcard for the middle of the user-entered string.  Maybe the solution is to update the data to include both 275 13th Street and 275 W 13th Street.  That would be a workaround, anyway.

  0
• 

  Permanently deleted user

  6. November 2013 13:51

  Yeah it's pretty close. You will need to split the string if you want to match the address with words missing in between. Then you will have to add an AND LIKE statement for each word so... (I think string.split(" ") will give you an array of strings)

  addresses = userInputString.split(' ')  // addresses is a string array

  Your query will end up looking something like this below after you run it through a for loop or however you want to create it with your string array:

  "UPPER (ADDRESS) LIKE UPPER('" + "%" + addresses[0] + "%" + "') AND UPPER (ADDRESS) LIKE UPPER('" + "%" + addresses[1] + "%" + "')""

  EDIT: my VB syntax is terrible here btw. split actually accepts and array of char as an argument and I honestly don't know how to do it inline for vb in a workflow. take that as psuedo code. You may have to create a variable of char[] and assign it the value {' '} and use that as the argument or it's possibly --> ("my address to split").Split(New [Char]() {" "c})  //wow <that is really not simple

   

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.