SteveL13
asked on
Add date range to query
One of the experts helped me with an SQL query a couple of weeks ago. Here is the result which works fine...
SELECT a.reason1, Count(a.reason1) AS CountOfreason1
FROM (select reason1 from tblJobsToAnalyzeFromSQLvie w
union all
select reason2 from tblJobsToAnalyzeFromSQLvie w
union all
select reason3 from tblJobsToAnalyzeFromSQLvie w
union all
select reason4 from tblJobsToAnalyzeFromSQLvie w
) AS a
GROUP BY a.reason1
HAVING (((Count(a.reason1))>0));
But now I need to add a date range criteria somehow and don't know how to code it. The form I'm using has two date type text boxes... txtStart and txtEnd. The table is tblJobsToAnalyzeFromSQLvie w and the field the date range criteria has to work against is "LastInvoice".
Thoughts?
SELECT a.reason1, Count(a.reason1) AS CountOfreason1
FROM (select reason1 from tblJobsToAnalyzeFromSQLvie
union all
select reason2 from tblJobsToAnalyzeFromSQLvie
union all
select reason3 from tblJobsToAnalyzeFromSQLvie
union all
select reason4 from tblJobsToAnalyzeFromSQLvie
) AS a
GROUP BY a.reason1
HAVING (((Count(a.reason1))>0));
But now I need to add a date range criteria somehow and don't know how to code it. The form I'm using has two date type text boxes... txtStart and txtEnd. The table is tblJobsToAnalyzeFromSQLvie
Thoughts?
or this
SELECT a.reason1, Count(a.reason1) AS CountOfreason1
FROM (select reason1 from tblJobsToAnalyzeFromSQLvie w where LastInvoice between cvdate(Forms!Form1!txtStar t) and cvdate(Forms!Form1!txtEnd)
union all
select reason2 from tblJobsToAnalyzeFromSQLvie w where LastInvoice between cvdate(Forms!Form1!txtStar t) and cvdate(Forms!Form1!txtEnd)
union all
select reason3 from tblJobsToAnalyzeFromSQLvie w where LastInvoice between cvdate(Forms!Form1!txtStar t) and cvdate(Forms!Form1!txtEnd)
union all
select reason4 from tblJobsToAnalyzeFromSQLvie w where LastInvoice between cvdate(Forms!Form1!txtStar t) and cvdate(Forms!Form1!txtEnd)
) AS a
GROUP BY a.reason1
HAVING (((Count(a.reason1))>0));
SELECT a.reason1, Count(a.reason1) AS CountOfreason1
FROM (select reason1 from tblJobsToAnalyzeFromSQLvie
union all
select reason2 from tblJobsToAnalyzeFromSQLvie
union all
select reason3 from tblJobsToAnalyzeFromSQLvie
union all
select reason4 from tblJobsToAnalyzeFromSQLvie
) AS a
GROUP BY a.reason1
HAVING (((Count(a.reason1))>0));
A short note on dates, which I seem to be mixed up with a lot lately!
Access ALWAYS stores a Date and Time value.
Format just messes with what you see, not with what is actually stored.
What is stored is actually a double precision (CDbl) value.
The decimal represents the hours portion (.25 is 6AM, .5 is 12PM, .75 is 6PM)
Why is this important?
If you have been storing Now(), and getting it displayed as dd-MMM-yy or any other date only format the time part is still there it's just not displayed.
Now, a BETWEEN operator may not give you the results you expect
If you tell it ...Where myDate between #1-Sep-11# and #13-Sep-11#, what you are actually telling it is ...Where myDate between #1-Sep-11 12:00:00 AM# and #13-Sep-11 12:00:00 AM#
If you have been storing times inadvertently, or even deliberately, the fact that you get only those values equal to 13-Sep-11 12:00:00 AM and not for the rest of September 13th is a bit disconcerting. If you had no values with a midnight time, you'd get no Sep 13th values
In plain English, when we ask for everything between the 1st and 13th, we expect to get everything from the 13th, not just those from the stroke of midnight.
Maybe you haven't used Now() or any other things that may have included a time portion. In that case, Access sees the time portion as 12:00:00 AM across the board and your BETWEEN operator works intuitively.
But it's worth mentioning
Access ALWAYS stores a Date and Time value.
Format just messes with what you see, not with what is actually stored.
What is stored is actually a double precision (CDbl) value.
The decimal represents the hours portion (.25 is 6AM, .5 is 12PM, .75 is 6PM)
Why is this important?
If you have been storing Now(), and getting it displayed as dd-MMM-yy or any other date only format the time part is still there it's just not displayed.
Now, a BETWEEN operator may not give you the results you expect
If you tell it ...Where myDate between #1-Sep-11# and #13-Sep-11#, what you are actually telling it is ...Where myDate between #1-Sep-11 12:00:00 AM# and #13-Sep-11 12:00:00 AM#
If you have been storing times inadvertently, or even deliberately, the fact that you get only those values equal to 13-Sep-11 12:00:00 AM and not for the rest of September 13th is a bit disconcerting. If you had no values with a midnight time, you'd get no Sep 13th values
In plain English, when we ask for everything between the 1st and 13th, we expect to get everything from the 13th, not just those from the stroke of midnight.
Maybe you haven't used Now() or any other things that may have included a time portion. In that case, Access sees the time portion as 12:00:00 AM across the board and your BETWEEN operator works intuitively.
But it's worth mentioning
well, just in case, that is no big deal for you can always use
datevalue(dateField) to get rid of the time portion of the date field.
datevalue(dateField) to get rid of the time portion of the date field.
<grin>
There are many ways to avoid stepping on a landmine...if you know it's there.
The fun-with-dates landmine tends to be one you don't realize is there...til you've already stepped on it.
Which is why I posted!
There are many ways to avoid stepping on a landmine...if you know it's there.
The fun-with-dates landmine tends to be one you don't realize is there...til you've already stepped on it.
Which is why I posted!
ASKER
I made a mistake in my original question. LastInvoice is actually Last Invoice (with a space). So what would the code be for that?
--Steve
--Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect!!!
SELECT a.reason1, Count(a.reason1) AS CountOfreason1
FROM (select reason1 from tblJobsToAnalyzeFromSQLvie
union all
select reason2 from tblJobsToAnalyzeFromSQLvie
union all
select reason3 from tblJobsToAnalyzeFromSQLvie
union all
select reason4 from tblJobsToAnalyzeFromSQLvie
) AS a
GROUP BY a.reason1
HAVING (((Count(a.reason1))>0));