simple SQL statement? 1

[kevinwilson]

hello all,
hoping someone can assist. the following statement grabs all fields and displays all the data, however i'd like the query to display only "active" records but i'm having difficulty with the "WHERE" clause. The fieldname is "Active" and is y/n type.

this works but retrieves all records, active and inactive.
strSQL = "SELECT " & strDBTable & "SysID.*" _
& " FROM " & strDBTable & "SysID " _
& " ORDER BY " & strDBTable & "SysID.SystemID "

in the statement below i'm trying to retrieve only active records where the "active" field is true...no success with this tho:
strSQL = "SELECT " & strDBTable & "SysID.*" _
& " FROM " & strDBTable & "SysID " _
& " WHERE " & strDBTable & "SysID.Active='"true'"
& " ORDER BY " & strDBTable & "SysID.SystemID "

Any help will be greatly appreciated.

Mahalo

 

[DreXor]

Y/N is easiest updated with 1/0 respectively

easiest thing to do is design view of the table, click the field in the boxes below there's a Format setting, it wont affect in DB use if you change it to True/False and it'll make writing queries easier externally

also, might be because you're encasing your True/False(s) in quotes:

& " WHERE " & strDBTable & "SysID.Active='"true'"

try this :

& " WHERE " & strDBTable & "SysID.Active=true"


DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[ToddWW]

Not sure what database your using. I use FoxPro and on my logical fields which are True/False, my SQL statements use 1 and 0. You might try.

WHERE yourY/Nfield = 0

ToddWW

 

[kevinwilson]

Many mahalo's for the quick response!

Drexor, your suggestion was right on...I removed the quotes and all worked perfectly. Funny, I thought I tried every possible scenario with the true, false, 1, 2, w/quotes, w/o qoutes?

ToddWW, I even tried your suggestion prior to this post but it seems like I was having a problem with the quotes from the beginning!

Thanks again