homepage Welcome to WebmasterWorld Guest from 54.196.195.158
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
Forum Library, Charter, Moderators: ocean10000

Microsoft IIS Web Server and ASP.NET Forum

    
Access Database Question
kevinj




msg:947578
 6:17 pm on Sep 26, 2003 (gmt 0)

I'm trying to only display records where the field SHARE_INFO is = No. I'm almost positive my code is correct, but I can't get it to work. Is it possible that the field in the database is corrupted? If so, is there a way to fix that?

My select statement is:

Set RS = DB.Execute("SELECT * FROM registrants WHERE share_info <> 'No' ORDER BY last_name ASC")

I've changed it to check a value in another field and it works fine. But no matter what I do with the SHARE_INFO field I get no records returned.

Thanks for any help.

 

txbakers




msg:947579
 6:20 pm on Sep 26, 2003 (gmt 0)

leave off the whereclause and just produce a list of values.

Take a look at what some of the actual values of that SHARE_INFO field look like. If you set the db field to be an Access Yes/No box, it might be populated with Y or N instead of Yes and No.

I don't know your database design though so I can only guess.

kevinj




msg:947580
 6:34 pm on Sep 26, 2003 (gmt 0)

I've left the Where clause on another page and it returns all records fine. The SHARE_INFO field is set up as a standard text datatype. The values are either blank or No.

mattur




msg:947581
 6:59 pm on Sep 26, 2003 (gmt 0)

Not sure if I've misunderstood your msg... but if you want all records where SHARE_INFO = No then your SQL should be:

SELECT * FROM registrants WHERE share_info = 'No'

share_info <> 'No' means where field value is NOT 'No'.

Otherwise, best way to check your sql is copy and paste it into a new query in the Access db to check it. HTH

mattglet




msg:947582
 7:15 pm on Sep 26, 2003 (gmt 0)

mattur is right... your original query is looking for records that are NOT No. you need " = NO"

-Matt

kevinj




msg:947583
 7:54 pm on Sep 26, 2003 (gmt 0)

Woops. I meant I want all records where SHARE_INFO is not equal to No. So the statement is correct.

WebJoe




msg:947584
 8:44 pm on Sep 26, 2003 (gmt 0)

The values are either blank or No.

have you tried
WHERE (share_info IS NULL or share_info = '')
what's the result then?

kevinj




msg:947585
 8:50 pm on Sep 26, 2003 (gmt 0)

WHERE share_info is NULL worked perfect.

Thank you!

jimmykav




msg:947586
 9:27 pm on Sep 26, 2003 (gmt 0)

The only test that works with null values is "IS NULL". Trying to use =, >=, <> etc. can produce strange results.

WebJoe




msg:947587
 10:22 pm on Sep 26, 2003 (gmt 0)

good point jimmykav, I forgot to point that one out. NULL is not a value, it's no value (a state), therefore cannot be compared to anything.

@kevinj: I'd use the whole statement as I posted it. Depending on the DBMS and the front end (where a user can change a 'No' to an empty value or an emtpy string), the value could end up being '' and not NULL.

wackal




msg:947588
 12:24 am on Sep 28, 2003 (gmt 0)

try using "where share_info = -1" OR "where share_info = 0"

I forget which one is yes and which is no, but that should work for you.

mattglet




msg:947589
 1:19 pm on Sep 28, 2003 (gmt 0)

1 = yes
0 = no

-Matt

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved