Welcome to WebmasterWorld Guest from 54.167.153.63

Forum Moderators: ocean10000

Message Too Old, No Replies

Access Database Question

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

10+ Year Member



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.

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

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.

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

10+ Year Member



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.
6:59 pm on Sep 26, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

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

WebmasterWorld Senior Member 10+ Year Member



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

-Matt

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

10+ Year Member



Woops. I meant I want all records where SHARE_INFO is not equal to No. So the statement is correct.
8:44 pm on Sep 26, 2003 (gmt 0)

10+ Year Member



The values are either blank or No.

have you tried

WHERE (share_info IS NULL or share_info = '')

what's the result then?
8:50 pm on Sep 26, 2003 (gmt 0)

10+ Year Member



WHERE share_info is NULL worked perfect.

Thank you!

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

10+ Year Member



The only test that works with null values is "IS NULL". Trying to use =, >=, <> etc. can produce strange results.
10:22 pm on Sep 26, 2003 (gmt 0)

10+ Year Member



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.

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

10+ Year Member



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.

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

WebmasterWorld Senior Member 10+ Year Member



1 = yes
0 = no

-Matt

 

Featured Threads

Hot Threads This Week

Hot Threads This Month