Welcome to WebmasterWorld Guest from 23.22.182.29

Forum Moderators: ocean10000

Message Too Old, No Replies

Access Database Question

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

Full Member

10+ Year Member

joined:Apr 8, 2002
posts:242
votes: 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.

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

Senior Member

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

joined:Sept 1, 2001
posts:4392
votes: 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.

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

Full Member

10+ Year Member

joined:Apr 8, 2002
posts:242
votes: 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.
6:59 pm on Sept 26, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Jan 29, 2002
posts:980
votes: 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

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 12, 2002
posts:1482
votes: 0


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

-Matt

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

Full Member

10+ Year Member

joined:Apr 8, 2002
posts:242
votes: 0


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

Junior Member

10+ Year Member

joined:Jan 6, 2003
posts:193
votes: 0


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 Sept 26, 2003 (gmt 0)

Full Member

10+ Year Member

joined:Apr 8, 2002
posts:242
votes: 0


WHERE share_info is NULL worked perfect.

Thank you!

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

Preferred Member

10+ Year Member

joined:Mar 27, 2001
posts:392
votes: 0


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

Junior Member

10+ Year Member

joined:Jan 6, 2003
posts:193
votes: 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.

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

Full Member

10+ Year Member

joined:Feb 26, 2003
posts:212
votes: 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.

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 12, 2002
posts:1482
votes: 0


1 = yes
0 = no

-Matt