New topic: Search on boolean field
<http://forums.realsoftware.com/viewtopic.php?t=46538> Page 1 of 1 [ 7 posts ] Previous topic | Next topic Author Message ronw Post subject: Search on boolean fieldPosted: Sat Jan 12, 2013 3:10 pm Joined: Fri Oct 20, 2006 12:33 pm Posts: 14 I am attempting to query a table to find the rows with a check box value of True. Dim sql As String Dim searchterm As Boolean searchterm = True // the Xmas_Party field is a Boolean // this is the only statement I can get to work sql = "SELECT ID, First_Name, Last_Name, Home_Phone1, Email1 FROM Contacts WHERE Xmas_Party = 1 " If I change the value after the = to True or searchterm I get nothing. Why must it be the number value rather than True? _________________ Thanks, Ron Top s-copinger Post subject: Re: Search on boolean fieldPosted: Sat Jan 12, 2013 7:00 pm Joined: Fri Jan 05, 2007 12:21 pm Posts: 148 Location: Scotland RealSQLDatabase is SQLite, and doesn't work with TRUE and FALSE on booleans, just 1 and 0 http://www.sqlite.org/datatype3.html#boolean Other databases used in RB do work properly for booleans. Top DaveS Post subject: Re: Search on boolean fieldPosted: Sat Jan 12, 2013 7:03 pm Joined: Sun Aug 05, 2007 10:46 am Posts: 4412 Location: San Diego, CA Where Xmas_Party='True' It must be in quotes.. .and must be "True"... not true, or TRUE at least that works for me _________________ Dave Sisemore MacPro, OSX Lion 10.7.4 RB2012r1 Note : I am not interested in any solutions that involve custom Plug-ins of any kind Top ronw Post subject: Re: Search on boolean fieldPosted: Sun Jan 13, 2013 5:58 pm Joined: Fri Oct 20, 2006 12:33 pm Posts: 14 DaveS wrote:Where Xmas_Party='True' It must be in quotes.. .and must be "True"... not true, or TRUE at least that works for me I get a syntax error with: sql = "SELECT ID, First_Name, Last_Name, Home_Phone1, Email1 FROM Contacts WHERE Xmas_Party = ' "True" ' " _________________ Thanks, Ron Top timhare Post subject: Re: Search on boolean fieldPosted: Sun Jan 13, 2013 6:02 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 11982 Location: Portland, OR USA Too many quotes. In DaveS' second reference the " around "True" are not intended to be part of the sql. sql = "SELECT ID, First_Name, Last_Name, Home_Phone1, Email1 FROM Contacts WHERE Xmas_Party = 'True' " Top ronw Post subject: Re: Search on boolean fieldPosted: Sun Jan 13, 2013 6:11 pm Joined: Fri Oct 20, 2006 12:33 pm Posts: 14 timhare wrote:Too many quotes. In DaveS' second reference the " around "True" are not intended to be part of the sql. sql = "SELECT ID, First_Name, Last_Name, Home_Phone1, Email1 FROM Contacts WHERE Xmas_Party = 'True' " "SELECT ID, First_Name, Last_Name, Home_Phone1, Email1 FROM Contacts WHERE Xmas_Party = 'True' " returns 0 records. "SELECT ID, First_Name, Last_Name, Home_Phone1, Email1 FROM Contacts WHERE Xmas_Party = 1 " returns 26 records (which is the correct number). The field data is saved by: rec.BooleanColumn ("Xmas_Party") = ckbParty.Value I'm unsure as to what is going wrong but... _________________ Thanks, Ron Top timhare Post subject: Re: Search on boolean fieldPosted: Sun Jan 13, 2013 6:57 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 11982 Location: Portland, OR USA How are you storing the values in the first place? There's likely a difference between how you do it and how DaveS does. Regardless, if you found something that works for you, go with it. Sqlite is just a little squirelly when it comes to data types like boolean where there are multiple ways to represent it. Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 7 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
