Why won't this select Query return any rows?

Hello, this is my first post here, i hope that someone can help me out.

I have a pretty complex SQL query, which is supposed to return a count of how many items are within a given time period.

however, there is a little "twist" to this, the items can open and close. so they have to be open during the period in question.

which means, that there is one of 5 possible criteria that will satisfy this, either they:

open before, and close after the period
open before, and close within the period
open within, and close within the period
open within, and close after the period

so, this is the SQL that i was using:

SELECT COUNT(*) FROM TBL_BQDATA WHERE ((DATA_ADDEDDATETIME > 6/1/2002 AND Closed_DATE <= 6/30/2002) OR (DATA_ADDEDDATETIME <= 6/1/2002 AND Closed_DATE > 6/30/2002) OR (DATA_ADDEDDATETIME <= 6/30/2002 AND Closed_DATE > 6/30/2002 AND DATA_ADDEDDATETIME > 6/1/2002) OR (DATA_ADDEDDATETIME <= 6/1/2002 AND Closed_DATE <= 6/30/2002 AND Closed_DATE > 6/1/2002)) AND (DATA_ADDEDDATETIME <= Closed_DATE);

The logic seems to be solid, but, i get no rows back. I have verified that there is in fact data in the table. I tried running this in the query builder in MS-Access 2002, and i got no results back as well. I took a screen shot to be clear, it shows the table data, as well as the query, one version nicely formatted, and one of just raw characters.



why don't i get anything back?

firstly, i dont actually know SQL, so im going to be brief, basically what i would do is start from a small query,

SELECT count(*) FROM TBL_BQDATA

and build up your query from there, testing each time you add a condition,

apart from that your query looks like it should work??

make sure that your test data, covers all possibilities as well

yep, start small and build up, that's what i'm doing. i've already built tons of graphs the small way.

thanks anyway...

on secon look at your query try this as the last bit

Closed_DATE > 6/1/2002 AND DATA_ADDEDDATETIME <= Closed_DATE));

ive got no idea if it will work, but give it a try

uh.. isn't that what i did write?

I put:

Closed_DATE > 6/1/2002)) AND (DATA_ADDEDDATETIME <= Closed_DATE);

you said:

Closed_DATE > 6/1/2002 AND DATA_ADDEDDATETIME <= Closed_DATE));

The only difference is the parentheses. in any case, the last condition, is only a check to make sure that the open date is before the closed, to ensure that nothign weird is going on.

i thought that might be the problem too, so i removed the entire last condition, it still doesn't work.

hmmm, im just having a stab in the dark here but maybe try this???


SELECT COUNT(*) FROM TBL_BQDATA WHERE (DATA_ADDEDDATETIME > 6/1/2002 AND Closed_DATE <= 6/30/2002 OR DATA_ADDEDDATETIME <= 6/1/2002 AND Closed_DATE > 6/30/2002 OR DATA_ADDEDDATETIME <= 6/30/2002 AND Closed_DATE > 6/30/2002 AND DATA_ADDEDDATETIME > 6/1/2002 OR DATA_ADDEDDATETIME <= 6/1/2002 AND Closed_DATE <= 6/30/2002 AND Closed_DATE > 6/1/2002 AND DATA_ADDEDDATETIME <= Closed_DATE);

I am such an idiot. you know what the problem was?

I forgot to put # signs around the dates.

i guess hash can fix anything!

lol, im supprised no one picked it up, even my *slams head against desk*

DOH

Logic could also be simplified to (pseudocode)

(opendate < end of period) AND (close date > start of period)