SQL QUERY !!!

[locationsetc]

Hello friends,

I have a little doubt on sql query. Iam using MS Access table. In table, I have a field called 'franchise' with datatype Yes/No(check box). i want to retrive records where franchise equal to true(or yes). how do we write the query for that? i have tried several ways but it did not work. here are some i tried:

SELECT * FROM EMAILcontacts WHERE franchise=1
SELECT * FROM EMAILcontacts WHERE franchise=True
SELECT * FROM EMAILcontacts WHERE franchise=Yes

the error is :
Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.

with regards,
Joseph.

 

[jemminger]

if the actual field value is stored as a string and not a boolean, you'll have to do something like

SELECT * FROM EMAILcontacts WHERE franchise = 'True'
SELECT * FROM EMAILcontacts WHERE franchise = 'Yes'




=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }

 

[DreXor]

you can also get by this with =True as you attempted originally, but you'll have to change the field settings ( design view in the bottom half ) field typeis yes no, but you can change how it's stored. ( i think there's 3 options, Y/N T/F and On/Off )

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

 

[locationsetc]

thanks jeff & drex

I got it !!

joseph

 

[snowboardr]

Using a number value will be more efficient 0 = now 1= yes

www.sitesd.com
ASP WEB DEVELOPMENT

 

[Genimuse]

Really? Isn't the tokenizer smarter than that?

 

[snowboardr]

tokenizer?

www.sitesd.com
ASP WEB DEVELOPMENT

 

[Genimuse]

When the database is interpreting the SQL it's parsing through the string, most likely (you do this in all of the interpreters I've written and studied, but I can't say I know for sure what happens in Access) replacing keywords with tokens, basically things the database actually understands. So -- and it wouldn't be just like this, but it's the same concept -- the interpreter might replace "INSERT" with [1], and "SELECT" with [2]. Checking a binary field it should replace "1" or "TRUE" or "YES" with the token for, effectively, the "BIT ON" token, replacing "0" or "FALSE" or "NO" with "BIT OFF" token (if during the interpretation it knew that it was comparing it to a field).

As such, I'd think that there would be no speed difference no matter what you used. But again, I didn't write Access (or MySQL, or SQL Server, or Oracle, etc.), so I don't know how they do it for sure. It's just likely that they're tokenizing, or more likely creating some form of logic trees of tokens.

 

[Genimuse]

The point of my tokenizer post was that there's no way that Access is comparing the value of binary field x to "T R U E" or "Y E S" -- it's comparing ideas.

It's still true that comparing numbers is going to be way faster than comparing strings where that is occurring -- it's just not what's happening when SQL is being interpreted in this example.