Sum based on date range

[Guest]

I need to measure the tallies in the "SEVERITY" column over the last six
months from the date range in the "START_DATE" column. I hope that makes
since?

Table Structure:
UNIQUE_ID, EMP_ID, START_DATE, END_DATE, NOTES, SEVERITY, N_DAYS

I need to be able to query by EMP_ID total number SEVERITY over the last 180
days from today backwards. It will be a rolling measurement of severity based
on the START_DATE issues. Output takes 1250 records and groups them by
EMP_ID to look like:

EMP_ID SEVERITY
0284 9
0917 11
1148 3
1375 0
1480 6
etc.....

I hope someone has an idea how to do this...... I have tried and tried
Thanks for your help or advice!

 

[Van T. Dinh]

I am not sire from your description, especially about 1250 records but try
something like:

========
SELECT EMP_ID, Sum(SEVERITY)
FROM [YourTable]
WHERE [START_DATE] Between DateAdd("d", -180, Date()) And Date()
GROUP BY EMP_ID
========

 

[Guest]

Thanks for the response Van,

I am really naive with Access, I started out thinking this would be a query,
but your answer looks like an SQL statement so I don't know what to do with
it?

Can you point me in the right diection?

I am not sire from your description, especially about 1250 records but try
something like:

========
SELECT EMP_ID, Sum(SEVERITY)
FROM [YourTable]
WHERE [START_DATE] Between DateAdd("d", -180, Date()) And Date()
GROUP BY EMP_ID
========

--
HTH
Van T. Dinh
MVP (Access)

I need to measure the tallies in the "SEVERITY" column over the last six
months from the date range in the "START_DATE" column. I hope that makes
since?

Table Structure:
UNIQUE_ID, EMP_ID, START_DATE, END_DATE, NOTES, SEVERITY, N_DAYS

I need to be able to query by EMP_ID total number SEVERITY over the last
180
days from today backwards. It will be a rolling measurement of severity
based
on the START_DATE issues. Output takes 1250 records and groups them by
EMP_ID to look like:

EMP_ID SEVERITY
0284 9
0917 11
1148 3
1375 0
1480 6
etc.....

I hope someone has an idea how to do this...... I have tried and tried
Thanks for your help or advice!

 

[Guest]

I have tried a crosstab Query, with no luck.
I tried pasting this script into a module, with no luck.
I have tried to make a Query using the script as critria, with no luck.
Tried to make it a macro, thats not close to being right.
Tried to build the statement in the Query, no luck.

I don't think I am even getting close yet! any other advice???? As you
can tell, I am lost as to what to do with the statement.

Thanks in advance, Rohn

I am not sire from your description, especially about 1250 records but try
something like:

========
SELECT EMP_ID, Sum(SEVERITY)
FROM [YourTable]
WHERE [START_DATE] Between DateAdd("d", -180, Date()) And Date()
GROUP BY EMP_ID
========

--
HTH
Van T. Dinh
MVP (Access)

I need to measure the tallies in the "SEVERITY" column over the last six
months from the date range in the "START_DATE" column. I hope that makes
since?

Table Structure:
UNIQUE_ID, EMP_ID, START_DATE, END_DATE, NOTES, SEVERITY, N_DAYS

I need to be able to query by EMP_ID total number SEVERITY over the last
180
days from today backwards. It will be a rolling measurement of severity
based
on the START_DATE issues. Output takes 1250 records and groups them by
EMP_ID to look like:

EMP_ID SEVERITY
0284 9
0917 11
1148 3
1375 0
1480 6
etc.....

I hope someone has an idea how to do this...... I have tried and tried
Thanks for your help or advice!

 

[John Spencer]

Open a new query
SELECT View: SQL from the menu
Paste the SQL statement into the window
Adjust field and table names

You can then try to switch back to design view or try to run the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have tried a crosstab Query, with no luck.
I tried pasting this script into a module, with no luck.
I have tried to make a Query using the script as critria, with no luck.
Tried to make it a macro, thats not close to being right.
Tried to build the statement in the Query, no luck.

I don't think I am even getting close yet! any other advice???? As you
can tell, I am lost as to what to do with the statement.

Thanks in advance, Rohn

I am not sire from your description, especially about 1250 records but
try
something like:

========
SELECT EMP_ID, Sum(SEVERITY)
FROM [YourTable]
WHERE [START_DATE] Between DateAdd("d", -180, Date()) And Date()
GROUP BY EMP_ID
========

--
HTH
Van T. Dinh
MVP (Access)

I need to measure the tallies in the "SEVERITY" column over the last six
months from the date range in the "START_DATE" column. I hope that
makes
since?

Table Structure:
UNIQUE_ID, EMP_ID, START_DATE, END_DATE, NOTES, SEVERITY, N_DAYS

I need to be able to query by EMP_ID total number SEVERITY over the
last
180
days from today backwards. It will be a rolling measurement of severity
based
on the START_DATE issues. Output takes 1250 records and groups them by
EMP_ID to look like:

EMP_ID SEVERITY
0284 9
0917 11
1148 3
1375 0
1480 6
etc.....

I hope someone has an idea how to do this...... I have tried and tried
Thanks for your help or advice!

 

[Rohn Everson]

John, Van or others,

I am still having a problem with the date range... it works if I take the
Where statement out, but I need the date query part. Here is what I have

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

It should be something like this...... but I can't get Now or Today to give
me todays date? And I am not sure why the Where clause has three date
references in it when I am trying to get today as one date and the date 6
months ago (to the day) as the other date. So here is what I tried to add.

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between Now(("d", -180, Date()) And Date()
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

Thanks again for any help.
Rohn

Open a new query
SELECT View: SQL from the menu
Paste the SQL statement into the window
Adjust field and table names

You can then try to switch back to design view or try to run the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have tried a crosstab Query, with no luck.
I tried pasting this script into a module, with no luck.
I have tried to make a Query using the script as critria, with no luck.
Tried to make it a macro, thats not close to being right.
Tried to build the statement in the Query, no luck.

I don't think I am even getting close yet! any other advice???? As you
can tell, I am lost as to what to do with the statement.

Thanks in advance, Rohn

I am not sire from your description, especially about 1250 records but
try
something like:

========
SELECT EMP_ID, Sum(SEVERITY)
FROM [YourTable]
WHERE [START_DATE] Between DateAdd("d", -180, Date()) And Date()
GROUP BY EMP_ID
========

--
HTH
Van T. Dinh
MVP (Access)



I need to measure the tallies in the "SEVERITY" column over the last
six
months from the date range in the "START_DATE" column. I hope that
makes
since?

Table Structure:
UNIQUE_ID, EMP_ID, START_DATE, END_DATE, NOTES, SEVERITY, N_DAYS

I need to be able to query by EMP_ID total number SEVERITY over the
last
180
days from today backwards. It will be a rolling measurement of
severity
based
on the START_DATE issues. Output takes 1250 records and groups them
by
EMP_ID to look like:

EMP_ID SEVERITY
0284 9
0917 11
1148 3
1375 0
1480 6
etc.....

I hope someone has an idea how to do this...... I have tried and
tried
Thanks for your help or advice!

 

[John Spencer]

Try the following. Note the change to the WHERE clause. If you want
180 days instead of 6 months change the DateAdd function to
DateAdd("d",-180,Date())


SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID


WHERE START_DATE Between DateAdd("m", -5, Date()) And Date()


GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

I still don't see a good reason tomake a separate table out of this
data, but perhaps you do.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John, Van or others,

I am still having a problem with the date range... it works if I take the
Where statement out, but I need the date query part. Here is what I have

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

It should be something like this...... but I can't get Now or Today to give
me todays date? And I am not sure why the Where clause has three date
references in it when I am trying to get today as one date and the date 6
months ago (to the day) as the other date. So here is what I tried to add.

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between Now(("d", -180, Date()) And Date()
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

Thanks again for any help.
Rohn

Open a new query
SELECT View: SQL from the menu
Paste the SQL statement into the window
Adjust field and table names

You can then try to switch back to design view or try to run the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have tried a crosstab Query, with no luck.
I tried pasting this script into a module, with no luck.
I have tried to make a Query using the script as critria, with no luck.
Tried to make it a macro, thats not close to being right.
Tried to build the statement in the Query, no luck.

I don't think I am even getting close yet! any other advice???? As you
can tell, I am lost as to what to do with the statement.

Thanks in advance, Rohn


:

I am not sire from your description, especially about 1250 records but
try
something like:

========
SELECT EMP_ID, Sum(SEVERITY)
FROM [YourTable]
WHERE [START_DATE] Between DateAdd("d", -180, Date()) And Date()
GROUP BY EMP_ID
========

--
HTH
Van T. Dinh
MVP (Access)



I need to measure the tallies in the "SEVERITY" column over the last
six
months from the date range in the "START_DATE" column. I hope that
makes
since?

Table Structure:
UNIQUE_ID, EMP_ID, START_DATE, END_DATE, NOTES, SEVERITY, N_DAYS

I need to be able to query by EMP_ID total number SEVERITY over the
last
180
days from today backwards. It will be a rolling measurement of
severity
based
on the START_DATE issues. Output takes 1250 records and groups them
by
EMP_ID to look like:

EMP_ID SEVERITY
0284 9
0917 11
1148 3
1375 0
1480 6
etc.....

I hope someone has an idea how to do this...... I have tried and
tried
Thanks for your help or advice!

 

[Rohn Everson]

Thanks for the help so far, I sure feel like I don't know what I am
doing..... I pasted in your suggested:

WHERE START_DATE Between DateAdd("d", -180, Date()) And Date()

and I get a compile error in query expression START_DATE Between
DateAdd("m", -5, Date()) And Date(). This seems simple, I can get it in
Crystal Reports but I need to run this as an update query back on another
table for reporting purposes.

Thanks again, Rohn

Try the following. Note the change to the WHERE clause. If you want 180
days instead of 6 months change the DateAdd function to
DateAdd("d",-180,Date())


SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID


WHERE START_DATE Between DateAdd("m", -5, Date()) And Date()


GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

I still don't see a good reason tomake a separate table out of this data,
but perhaps you do.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John, Van or others,

I am still having a problem with the date range... it works if I take the
Where statement out, but I need the date query part. Here is what I have

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

It should be something like this...... but I can't get Now or Today to
give me todays date? And I am not sure why the Where clause has three
date references in it when I am trying to get today as one date and the
date 6 months ago (to the day) as the other date. So here is what I
tried to add.

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between Now(("d", -180, Date()) And Date()
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

Thanks again for any help.
Rohn

Open a new query
SELECT View: SQL from the menu
Paste the SQL statement into the window
Adjust field and table names

You can then try to switch back to design view or try to run the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have tried a crosstab Query, with no luck.
I tried pasting this script into a module, with no luck.
I have tried to make a Query using the script as critria, with no luck.
Tried to make it a macro, thats not close to being right.
Tried to build the statement in the Query, no luck.

I don't think I am even getting close yet! any other advice???? As
you
can tell, I am lost as to what to do with the statement.

Thanks in advance, Rohn


:

I am not sire from your description, especially about 1250 records but
try
something like:

========
SELECT EMP_ID, Sum(SEVERITY)
FROM [YourTable]
WHERE [START_DATE] Between DateAdd("d", -180, Date()) And Date()
GROUP BY EMP_ID
========

--
HTH
Van T. Dinh
MVP (Access)



message
I need to measure the tallies in the "SEVERITY" column over the last
six
months from the date range in the "START_DATE" column. I hope that
makes
since?

Table Structure:
UNIQUE_ID, EMP_ID, START_DATE, END_DATE, NOTES, SEVERITY, N_DAYS

I need to be able to query by EMP_ID total number SEVERITY over the
last
180
days from today backwards. It will be a rolling measurement of
severity
based
on the START_DATE issues. Output takes 1250 records and groups them
by
EMP_ID to look like:

EMP_ID SEVERITY
0284 9
0917 11
1148 3
1375 0
1480 6
etc.....

I hope someone has an idea how to do this...... I have tried and
tried
Thanks for your help or advice!

 

[John Spencer]

Let's try to narrow the problem down.

Try JUST the SELECT QUERY without any Insert statement.

Try entering some actual dates and see if you get an error.

WHERE Start_Date Between #09/30/2007# and #02/28/2007#

If you do get an error then what type of field is Start_Date?


SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between #9/30/2006# And #2/28/2007#
AND dbo_EMPLOYEE.PAY_STATUS="A"
GROUP BY dbo_EMP_ABSENCE.EMP_ID


Next Try:
SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between DateAdd("m", -5, Date()) And Date()
AND dbo_EMPLOYEE.PAY_STATUS="A"
GROUP BY dbo_EMP_ABSENCE.EMP_ID

If that errors, then post back with the entire error message.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks for the help so far, I sure feel like I don't know what I am
doing..... I pasted in your suggested:

WHERE START_DATE Between DateAdd("d", -180, Date()) And Date()

and I get a compile error in query expression START_DATE Between
DateAdd("m", -5, Date()) And Date(). This seems simple, I can get it in
Crystal Reports but I need to run this as an update query back on another
table for reporting purposes.

Thanks again, Rohn

Try the following. Note the change to the WHERE clause. If you want 180
days instead of 6 months change the DateAdd function to
DateAdd("d",-180,Date())


SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID


WHERE START_DATE Between DateAdd("m", -5, Date()) And Date()


GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

I still don't see a good reason tomake a separate table out of this data,
but perhaps you do.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John, Van or others,

I am still having a problem with the date range... it works if I take the
Where statement out, but I need the date query part. Here is what I have

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

It should be something like this...... but I can't get Now or Today to
give me todays date? And I am not sure why the Where clause has three
date references in it when I am trying to get today as one date and the
date 6 months ago (to the day) as the other date. So here is what I
tried to add.

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between Now(("d", -180, Date()) And Date()
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

Thanks again for any help.
Rohn


Open a new query
SELECT View: SQL from the menu
Paste the SQL statement into the window
Adjust field and table names

You can then try to switch back to design view or try to run the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have tried a crosstab Query, with no luck.
I tried pasting this script into a module, with no luck.
I have tried to make a Query using the script as critria, with no luck.
Tried to make it a macro, thats not close to being right.
Tried to build the statement in the Query, no luck.

I don't think I am even getting close yet! any other advice???? As
you
can tell, I am lost as to what to do with the statement.

Thanks in advance, Rohn


:

I am not sire from your description, especially about 1250 records but
try
something like:

========
SELECT EMP_ID, Sum(SEVERITY)
FROM [YourTable]
WHERE [START_DATE] Between DateAdd("d", -180, Date()) And Date()
GROUP BY EMP_ID
========

--
HTH
Van T. Dinh
MVP (Access)



message
I need to measure the tallies in the "SEVERITY" column over the last
six
months from the date range in the "START_DATE" column. I hope that
makes
since?

Table Structure:
UNIQUE_ID, EMP_ID, START_DATE, END_DATE, NOTES, SEVERITY, N_DAYS

I need to be able to query by EMP_ID total number SEVERITY over the
last
180
days from today backwards. It will be a rolling measurement of
severity
based
on the START_DATE issues. Output takes 1250 records and groups them
by
EMP_ID to look like:

EMP_ID SEVERITY
0284 9
0917 11
1148 3
1375 0
1480 6
etc.....

I hope someone has an idea how to do this...... I have tried and
tried
Thanks for your help or advice!

 

[Michael Gramelspacher]

Thanks for the help so far, I sure feel like I don't know what I am
doing..... I pasted in your suggested:

WHERE START_DATE Between DateAdd("d", -180, Date()) And Date()

and I get a compile error in query expression START_DATE Between
DateAdd("m", -5, Date()) And Date(). This seems simple, I can get it in
Crystal Reports but I need to run this as an update query back on another
table for reporting purposes.

Thanks again, Rohn

See if this gets you anywhere:

BETWEEN DATEADD("d",DATEDIFF("d",0,NOW)-180,0)AND
DATEADD("d",DATEDIFF("d",0,NOW),0)

 

[Rohn]

I get an error message: Compile error, in query expression 'START_DATE
Between DateAdd("d", -180, Date()) and Date()'.

on the second script, the database crashes and asks if I want to send a
report to Microsoft. The data type of the START_DATE field is date/time.

John, your a genius....... the data type seems to be the issue!
So far this gets the right data if I manually put the date/time into the
statement: The statement below does work, is there a statement that
collects the date/time?

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY, dbo_EMPLOYEE.PAY_STATUS INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON dbo_EMPLOYEE.ID =
dbo_EMP_ABSENCE.EMP_ID
WHERE (((dbo_EMP_ABSENCE.START_DATE) Between #9/5/2006 12:1:0# And
#2/28/2007 12:1:0#))
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

Let's try to narrow the problem down.

Try JUST the SELECT QUERY without any Insert statement.

Try entering some actual dates and see if you get an error.

WHERE Start_Date Between #09/30/2007# and #02/28/2007#

If you do get an error then what type of field is Start_Date?


SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between #9/30/2006# And #2/28/2007#
AND dbo_EMPLOYEE.PAY_STATUS="A"
GROUP BY dbo_EMP_ABSENCE.EMP_ID


Next Try:
SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between DateAdd("m", -5, Date()) And Date()
AND dbo_EMPLOYEE.PAY_STATUS="A"
GROUP BY dbo_EMP_ABSENCE.EMP_ID

If that errors, then post back with the entire error message.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks for the help so far, I sure feel like I don't know what I am
doing..... I pasted in your suggested:

WHERE START_DATE Between DateAdd("d", -180, Date()) And Date()

and I get a compile error in query expression START_DATE Between
DateAdd("m", -5, Date()) And Date(). This seems simple, I can get it in
Crystal Reports but I need to run this as an update query back on another
table for reporting purposes.

Thanks again, Rohn

Try the following. Note the change to the WHERE clause. If you want 180
days instead of 6 months change the DateAdd function to
DateAdd("d",-180,Date())


SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID


WHERE START_DATE Between DateAdd("m", -5, Date()) And Date()


GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

I still don't see a good reason tomake a separate table out of this data,
but perhaps you do.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Rohn Everson wrote:
John, Van or others,

I am still having a problem with the date range... it works if I take the
Where statement out, but I need the date query part. Here is what I have

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

It should be something like this...... but I can't get Now or Today to
give me todays date? And I am not sure why the Where clause has three
date references in it when I am trying to get today as one date and the
date 6 months ago (to the day) as the other date. So here is what I
tried to add.

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between Now(("d", -180, Date()) And Date()
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

Thanks again for any help.
Rohn


Open a new query
SELECT View: SQL from the menu
Paste the SQL statement into the window
Adjust field and table names

You can then try to switch back to design view or try to run the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have tried a crosstab Query, with no luck.
I tried pasting this script into a module, with no luck.
I have tried to make a Query using the script as critria, with no luck.
Tried to make it a macro, thats not close to being right.
Tried to build the statement in the Query, no luck.

I don't think I am even getting close yet! any other advice???? As
you
can tell, I am lost as to what to do with the statement.

Thanks in advance, Rohn


:

I am not sire from your description, especially about 1250 records but
try
something like:

========
SELECT EMP_ID, Sum(SEVERITY)
FROM [YourTable]
WHERE [START_DATE] Between DateAdd("d", -180, Date()) And Date()
GROUP BY EMP_ID
========

--
HTH
Van T. Dinh
MVP (Access)



message
I need to measure the tallies in the "SEVERITY" column over the last
six
months from the date range in the "START_DATE" column. I hope that
makes
since?

Table Structure:
UNIQUE_ID, EMP_ID, START_DATE, END_DATE, NOTES, SEVERITY, N_DAYS

I need to be able to query by EMP_ID total number SEVERITY over the
last
180
days from today backwards. It will be a rolling measurement of
severity
based
on the START_DATE issues. Output takes 1250 records and groups them
by
EMP_ID to look like:

EMP_ID SEVERITY
0284 9
0917 11
1148 3
1375 0
1480 6
etc.....

I hope someone has an idea how to do this...... I have tried and
tried
Thanks for your help or advice!

 

[Rohn]

I get an error message: Compile error, in query expression 'START_DATE
Between DateAdd("d", -180, Date()) and Date()'.

on the second script, the database crashes and asks if I want to send a
report to Microsoft. The data type of the START_DATE field is date/time.

John, your a genius....... the data type seems to be the issue! So far this
gets the right data if I manually put the date/time into the statement: The
statement below does work, is there a statement that collects the date/time?

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY, dbo_EMPLOYEE.PAY_STATUS INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON dbo_EMPLOYEE.ID =
dbo_EMP_ABSENCE.EMP_ID
WHERE (((dbo_EMP_ABSENCE.START_DATE) Between #9/5/2006 12:1:0# And
#2/28/2007 12:1:0#))
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

Let's try to narrow the problem down.

Try JUST the SELECT QUERY without any Insert statement.

Try entering some actual dates and see if you get an error.

WHERE Start_Date Between #09/30/2007# and #02/28/2007#

If you do get an error then what type of field is Start_Date?


SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between #9/30/2006# And #2/28/2007#
AND dbo_EMPLOYEE.PAY_STATUS="A"
GROUP BY dbo_EMP_ABSENCE.EMP_ID


Next Try:
SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between DateAdd("m", -5, Date()) And Date()
AND dbo_EMPLOYEE.PAY_STATUS="A"
GROUP BY dbo_EMP_ABSENCE.EMP_ID

If that errors, then post back with the entire error message.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks for the help so far, I sure feel like I don't know what I am
doing..... I pasted in your suggested:

WHERE START_DATE Between DateAdd("d", -180, Date()) And Date()

and I get a compile error in query expression START_DATE Between
DateAdd("m", -5, Date()) And Date(). This seems simple, I can get it in
Crystal Reports but I need to run this as an update query back on another
table for reporting purposes.

Thanks again, Rohn

Try the following. Note the change to the WHERE clause. If you want 180
days instead of 6 months change the DateAdd function to
DateAdd("d",-180,Date())


SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID


WHERE START_DATE Between DateAdd("m", -5, Date()) And Date()


GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

I still don't see a good reason tomake a separate table out of this data,
but perhaps you do.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Rohn Everson wrote:
John, Van or others,

I am still having a problem with the date range... it works if I take the
Where statement out, but I need the date query part. Here is what I have

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

It should be something like this...... but I can't get Now or Today to
give me todays date? And I am not sure why the Where clause has three
date references in it when I am trying to get today as one date and the
date 6 months ago (to the day) as the other date. So here is what I
tried to add.

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between Now(("d", -180, Date()) And Date()
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

Thanks again for any help.
Rohn


Open a new query
SELECT View: SQL from the menu
Paste the SQL statement into the window
Adjust field and table names

You can then try to switch back to design view or try to run the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have tried a crosstab Query, with no luck.
I tried pasting this script into a module, with no luck.
I have tried to make a Query using the script as critria, with no luck.
Tried to make it a macro, thats not close to being right.
Tried to build the statement in the Query, no luck.

I don't think I am even getting close yet! any other advice???? As
you
can tell, I am lost as to what to do with the statement.

Thanks in advance, Rohn


:

I am not sire from your description, especially about 1250 records but
try
something like:

========
SELECT EMP_ID, Sum(SEVERITY)
FROM [YourTable]
WHERE [START_DATE] Between DateAdd("d", -180, Date()) And Date()
GROUP BY EMP_ID
========

--
HTH
Van T. Dinh
MVP (Access)



message
I need to measure the tallies in the "SEVERITY" column over the last
six
months from the date range in the "START_DATE" column. I hope that
makes
since?

Table Structure:
UNIQUE_ID, EMP_ID, START_DATE, END_DATE, NOTES, SEVERITY, N_DAYS

I need to be able to query by EMP_ID total number SEVERITY over the
last
180
days from today backwards. It will be a rolling measurement of
severity
based
on the START_DATE issues. Output takes 1250 records and groups them
by
EMP_ID to look like:

EMP_ID SEVERITY
0284 9
0917 11
1148 3
1375 0
1480 6
etc.....

I hope someone has an idea how to do this...... I have tried and
tried
Thanks for your help or advice!

 

[John Spencer]

I suspect that you have a references problem.

To do its job, Access makes use of various external program and object
libraries. If you move a database from one machine to another, these
references may be "broken".

When this happens, you need to take steps to let Access repair the
reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING.

Here are MVP Doug Steele's instructions for how to do it:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.

**** End Quote ****

After you fix the references then you should be able to use

START_DATE Between DateAdd("d", -180, Date()) and Date()


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I get an error message: Compile error, in query expression 'START_DATE
Between DateAdd("d", -180, Date()) and Date()'.

on the second script, the database crashes and asks if I want to send a
report to Microsoft. The data type of the START_DATE field is date/time.

John, your a genius....... the data type seems to be the issue!
So far this gets the right data if I manually put the date/time into the
statement: The statement below does work, is there a statement that
collects the date/time?

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY, dbo_EMPLOYEE.PAY_STATUS INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON dbo_EMPLOYEE.ID =
dbo_EMP_ABSENCE.EMP_ID
WHERE (((dbo_EMP_ABSENCE.START_DATE) Between #9/5/2006 12:1:0# And
#2/28/2007 12:1:0#))
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

Let's try to narrow the problem down.

Try JUST the SELECT QUERY without any Insert statement.

Try entering some actual dates and see if you get an error.

WHERE Start_Date Between #09/30/2007# and #02/28/2007#

If you do get an error then what type of field is Start_Date?


SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between #9/30/2006# And #2/28/2007#
AND dbo_EMPLOYEE.PAY_STATUS="A"
GROUP BY dbo_EMP_ABSENCE.EMP_ID


Next Try:
SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between DateAdd("m", -5, Date()) And Date()
AND dbo_EMPLOYEE.PAY_STATUS="A"
GROUP BY dbo_EMP_ABSENCE.EMP_ID

If that errors, then post back with the entire error message.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks for the help so far, I sure feel like I don't know what I am
doing..... I pasted in your suggested:

WHERE START_DATE Between DateAdd("d", -180, Date()) And Date()

and I get a compile error in query expression START_DATE Between
DateAdd("m", -5, Date()) And Date(). This seems simple, I can get it in
Crystal Reports but I need to run this as an update query back on another
table for reporting purposes.

Thanks again, Rohn

Try the following. Note the change to the WHERE clause. If you want 180
days instead of 6 months change the DateAdd function to
DateAdd("d",-180,Date())


SELECT dbo_EMP_ABSENCE.EMP_ID
, Sum(dbo_EMP_ABSENCE.SEVERITY) AS SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID


WHERE START_DATE Between DateAdd("m", -5, Date()) And Date()


GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

I still don't see a good reason tomake a separate table out of this data,
but perhaps you do.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Rohn Everson wrote:
John, Van or others,

I am still having a problem with the date range... it works if I take the
Where statement out, but I need the date query part. Here is what I have

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

It should be something like this...... but I can't get Now or Today
to
give me todays date? And I am not sure why the Where clause has
three
date references in it when I am trying to get today as one date and the
date 6 months ago (to the day) as the other date. So here is what I
tried to add.

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON
dbo_EMPLOYEE.ID=dbo_EMP_ABSENCE.EMP_ID
WHERE START_DATE Between Now(("d", -180, Date()) And Date()
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

Thanks again for any help.
Rohn


Open a new query
SELECT View: SQL from the menu
Paste the SQL statement into the window
Adjust field and table names

You can then try to switch back to design view or try to run the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have tried a crosstab Query, with no luck.
I tried pasting this script into a module, with no luck.
I have tried to make a Query using the script as critria, with no luck.
Tried to make it a macro, thats not close to being right.
Tried to build the statement in the Query, no luck.

I don't think I am even getting close yet! any other advice???? As
you
can tell, I am lost as to what to do with the statement.

Thanks in advance, Rohn


:

I am not sire from your description, especially about 1250 records but
try
something like:

========
SELECT EMP_ID, Sum(SEVERITY)
FROM [YourTable]
WHERE [START_DATE] Between DateAdd("d", -180, Date()) And Date()
GROUP BY EMP_ID
========

--
HTH
Van T. Dinh
MVP (Access)



message
I need to measure the tallies in the "SEVERITY" column over the last
six
months from the date range in the "START_DATE" column. I hope that
makes
since?

Table Structure:
UNIQUE_ID, EMP_ID, START_DATE, END_DATE, NOTES, SEVERITY, N_DAYS

I need to be able to query by EMP_ID total number SEVERITY over the
last
180
days from today backwards. It will be a rolling measurement of
severity
based
on the START_DATE issues. Output takes 1250 records and groups them
by
EMP_ID to look like:

EMP_ID SEVERITY
0284 9
0917 11
1148 3
1375 0
1480 6
etc.....

I hope someone has an idea how to do this...... I have tried and
tried
Thanks for your help or advice!

 

[Rohn]

Guys,
OK, none of the references were borken.... I unchecked, closed, opened and
rechecked four of the six references and still I get the same compile error!

Any other ideas? I import the table into Access it works using the actual
dates/times, when I leave it connected by a link it does not work.

Sorry this is dragging on and on!

Getting frustrated, Rohn Everson

I suspect that you have a references problem.

To do its job, Access makes use of various external program and object
libraries. If you move a database from one machine to another, these
references may be "broken".

When this happens, you need to take steps to let Access repair the
reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING.

Here are MVP Doug Steele's instructions for how to do it:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.

**** End Quote ****

After you fix the references then you should be able to use

START_DATE Between DateAdd("d", -180, Date()) and Date()


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I get an error message: Compile error, in query expression 'START_DATE
Between DateAdd("d", -180, Date()) and Date()'.

on the second script, the database crashes and asks if I want to send a
report to Microsoft. The data type of the START_DATE field is date/time.

John, your a genius....... the data type seems to be the issue!
So far this gets the right data if I manually put the date/time into the
statement: The statement below does work, is there a statement that
collects the date/time?

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY, dbo_EMPLOYEE.PAY_STATUS INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON dbo_EMPLOYEE.ID =
dbo_EMP_ABSENCE.EMP_ID
WHERE (((dbo_EMP_ABSENCE.START_DATE) Between #9/5/2006 12:1:0# And
#2/28/2007 12:1:0#))
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

 

[John Spencer]

Where (in what application) is the table that you are linking to?

Can you write the query you need in that application? If so, perhaps you
can copy the SQL and use it in a pass-through query?

The only other things I can think of would be that you have a custom
function named Date, or a field named Date (or DateAdd) and those are
interfering with the process.

What happens if you use
Between Date()-180 and Date()

Do you still get the error?

How about if you try
Between DateAdd("d",-180,#2/1/2007#) and #2/1/2007#

Beyond that I am stumped. Especially since it works with the table imported
and not with the table when it is linked.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Guys,
OK, none of the references were borken.... I unchecked, closed, opened and
rechecked four of the six references and still I get the same compile
error!

Any other ideas? I import the table into Access it works using the actual
dates/times, when I leave it connected by a link it does not work.

Sorry this is dragging on and on!

Getting frustrated, Rohn Everson

I suspect that you have a references problem.

To do its job, Access makes use of various external program and object
libraries. If you move a database from one machine to another, these
references may be "broken".

When this happens, you need to take steps to let Access repair the
reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING.

Here are MVP Doug Steele's instructions for how to do it:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed,
and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back
out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that
contains
the specific function that's failing doesn't have a problem.

**** End Quote ****

After you fix the references then you should be able to use

START_DATE Between DateAdd("d", -180, Date()) and Date()


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I get an error message: Compile error, in query expression 'START_DATE
Between DateAdd("d", -180, Date()) and Date()'.

on the second script, the database crashes and asks if I want to send a
report to Microsoft. The data type of the START_DATE field is date/time.

John, your a genius....... the data type seems to be the issue!
So far this gets the right data if I manually put the date/time into
the
statement: The statement below does work, is there a statement that
collects the date/time?

SELECT dbo_EMP_ABSENCE.EMP_ID, Sum(dbo_EMP_ABSENCE.SEVERITY) AS
SumOfSEVERITY, dbo_EMPLOYEE.PAY_STATUS INTO [(T)Severity]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON dbo_EMPLOYEE.ID =
dbo_EMP_ABSENCE.EMP_ID
WHERE (((dbo_EMP_ABSENCE.START_DATE) Between #9/5/2006 12:1:0# And
#2/28/2007 12:1:0#))
GROUP BY dbo_EMP_ABSENCE.EMP_ID, dbo_EMPLOYEE.PAY_STATUS
HAVING (((dbo_EMPLOYEE.PAY_STATUS)="A"));

 

[Michael Gramelspacher]

Guys,
OK, none of the references were borken.... I unchecked, closed, opened and
rechecked four of the six references and still I get the same compile error!

Any other ideas? I import the table into Access it works using the actual
dates/times, when I leave it connected by a link it does not work.

Sorry this is dragging on and on!

Getting frustrated, Rohn Everson

and just to satisfy my curiosity, did this fail also?

BETWEEN DATEADD("d",DATEDIFF("d",0,NOW)-180,0) AND DATEADD
("d",DATEDIFF("d",0,NOW),0)