Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Faiyth
Starting Member
19 Posts |
 Posted - 2002-02-19 : 13:26:13
|
| Here's my problem. Maybe I just have too many criteria but something doesn't seem to be working. Here's the code and what it does: SELECT roisigndate, status FROM intake WHERE status='A' AND roisigndate BETWEEN '1/01/99' and '" & DateTo & "' OR roisigndate is Null ORDER BY servagency,roisigndate *This returns all records.. even the ones that do not have a status of 'A'. Here's the code and what it does: SELECT roisigndate, status FROM intake WHERE status='A' AND roisigndate BETWEEN '1/01/99' and '" & DateTo & "' ORDER BY servagency,roisigndate *This returns everything correctly.. the problem is I need to show the ones where roisigndate is null as well. Here's the code and what it does: SELECT roisigndate, status FROM intake WHERE roisigndate BETWEEN '1/01/99' and '" & DateTo & "' or roisigndate is Null AND status='A' ORDER BY servagency,roisigndate *This returns all records that have a roisigndate that is between the two specific fields and all the fields that are null, but it also returns records that's status is NOT A. What is going on? Do I have to much criteria? |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-02-19 : 13:34:52
|
| I think all you really need is a pair of extra parentheses in your WHERE clause: SELECT roisigndate, status FROM intake WHERE status='A' AND (roisigndate BETWEEN '1/01/99' and '" & DateTo & "' OR roisigndate is Null) ORDER BY servagency,roisigndate Edited by - izaltsman on 02/19/2002 13:35:46 |
 |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-02-19 : 13:39:33
|
quote:
SELECT roisigndate, status FROM intake WHERE status='A' AND roisigndate BETWEEN '1/01/99' and '" & DateTo & "' OR roisigndate is Null ORDER BY servagency,roisigndate
Try SELECT roisigndate, status FROM intake WHERE status='A' AND (roisigndate BETWEEN '1/01/99' and '" & DateTo & "' OR roisigndate is Null) ORDER BY servagency,roisigndate I haven't tried this is a sample database, but it should work. When you give the statement an or statement, it only has to meet this criteria. By placing parentheses around the appropriate statements, it should work. If my sample statement does not work, try moving the parentheses around and it should eventually give you the results you need. If this doesn't work, let me know and I will throw some test data into my database and look at it further.Sorry for duplicate reply. Looks like izaltsman beat me to it. Glad to see he confirmed what I was thinking.Edited by - joldham on 02/19/2002 13:41:06 |
|
|
|
Faiyth
Starting Member
19 Posts |
Posted - 2002-02-19 : 13:43:40
|
| Thanks that worked great! |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-19 : 13:45:51
|
| Faiyth,I'm not sure what you are trying to get?If status is not 'A' and roisigndate is NULL, do you want that one? My understanding of your post is no.So based on that try putting Parenthases around your second 2 criterea.ex. SELECT roisigndate, status FROM intake WHERE status='A' AND (roisigndate BETWEEN '1/01/99' and '" & DateTo & "' OR roisigndate is Null) ORDER BY servagency,roisigndate HTH-Chad |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-19 : 13:47:51
|
| Ahhhhh!I went to test it before hitting 'POST', and got beat by several of you.-Chad |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-19 : 18:53:13
|
Testing ? We don't need no stinkin testing! Damian |
|
|
|
|
|
|
|
|
Problem with SQL Statement