Access date range querie

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to calculate how many cases have been open for 11 months. How can i do
that in a querie.
 
This example assumes that ...

a) CaseClosed and CaseOpened are date/time fields. CaseClosed is left Null
until the case is closed, when the closing date is entered.

b) You want the count of all cases that i) are still open and ii) have been
open for 11 months or more.

SELECT Count(*) AS TheCount
FROM tblTest
WHERE (((tblTest.CaseClosed) Is Null) AND
((DateDiff("m",[CaseOpened],Date()))>=11));
 
I want to count cases that have been open for more than 11 months

Brendan Reynolds said:
This example assumes that ...

a) CaseClosed and CaseOpened are date/time fields. CaseClosed is left Null
until the case is closed, when the closing date is entered.

b) You want the count of all cases that i) are still open and ii) have been
open for 11 months or more.

SELECT Count(*) AS TheCount
FROM tblTest
WHERE (((tblTest.CaseClosed) Is Null) AND
((DateDiff("m",[CaseOpened],Date()))>=11));


--
Brendan Reynolds (MVP)


Jessica said:
I need to calculate how many cases have been open for 11 months. How can i
do
that in a querie.
Click to expand...
Click to expand...
 
Then just change >= to >

--
Brendan Reynolds (MVP)

Jessica said:
I want to count cases that have been open for more than 11 months

Brendan Reynolds said:
This example assumes that ...

a) CaseClosed and CaseOpened are date/time fields. CaseClosed is left
Null
until the case is closed, when the closing date is entered.

b) You want the count of all cases that i) are still open and ii) have
been
open for 11 months or more.

SELECT Count(*) AS TheCount
FROM tblTest
WHERE (((tblTest.CaseClosed) Is Null) AND
((DateDiff("m",[CaseOpened],Date()))>=11));


--
Brendan Reynolds (MVP)


Jessica said:
I need to calculate how many cases have been open for 11 months. How can
i
do
that in a querie.
Click to expand...
Click to expand...
Click to expand...
 
As you have not told me anything at all about the structure of your
database, that would be some neat trick, for me to supply a copy-and-paste
solution without knowing anything about your database. Unfortunately, I'm
not that clever.

You will need to adapt my example to your needs. At a minimum, you will
certainly need to change the name of the table, as it seems unlikely that
your table is actually called 'tblTest'. You may also need to change field
names, I don't know, because I don't know what your real field names are.
 
I have a database that has two option buttons one is Client ID and the other
one NAME. And i also have A Client ID label, First Name Lable, Last Name
Lable. When i click on Client ID Option Button i would like the cursor to
move to the Client ID label and when I clik on the Name Option botton i would
like the cursor to move to last Name label. Is this possible
 
Back
Top