Welcome to WebmasterWorld Guest from 54.162.232.51

This page requires javascript to function properly. It seems that your browser does not have Javascript enabled. Please enable Javascript and press the Reload/Refresh button on your browser.

Forum Moderators: ocean10000

Message Too Old, No Replies

Access Database Question

kevinj

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

Full 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.

txbakers

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

Senior Member

txbakers is a WebmasterWorld Top Contributor of All Time

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.

kevinj

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

Full 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.

mattur

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

Senior Member

joined:Jan 29, 2002
posts:981
votes: 1

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

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

Senior 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

kevinj

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

Full 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.

WebJoe

8:44 pm on Sept 26, 2003 (gmt 0)

Junior 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?

kevinj

8:50 pm on Sept 26, 2003 (gmt 0)

Full Member

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

WHERE share_info is NULL worked perfect.

Thank you!

jimmykav

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

Preferred 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.

WebJoe

10:22 pm on Sept 26, 2003 (gmt 0)

Junior 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.