Report based on a date range

G

Gina

I'm sorry if I'm wearing out my welcome...I posted a
similar question before but was told I didn't provide
enough details.

I want a report that lists ALL of our employees and
whether they're eligible for a bonus in any given month.
Bonus eligibility is determined by the number of absences,
tardies, written warnings, and suspensions an employee has
had during the month. I want to the report to include ALL
the employees and their bonus eligibility to give to the
payroll department.

Here's the query the report is based on:
SELECT qryBonusEligReport.Event,
qryBonusEligReport.EventDate, Nz([CtUnexAbs]) AS
CountUnexAbs, Nz([CtTard]) AS CountTard, Nz([CtLtEarly])
AS CountLtEarly, Nz([CtWarn]) AS CountWarn, Date()-
[HireDate] AS DaysEmp, Nz([CtSusp]) AS CountSusp,
EmployeeData.EmployeeName, EmployeeData.HireDate,
EmployeeData.FileNumber, EmployeeData.Department,
EmployeeData.Status, EmployeeData.BonusGroup
FROM EmployeeData LEFT JOIN qryBonusEligReport ON
EmployeeData.EmployeeName =
qryBonusEligReport.EmployeeName;

I have two main tables:
tblEvents tblEmployeeData
FileNumber FileNumber
Event EmployeeName
EventDate Department
EventID HireDate
Status
EmployeeID

I have the query working exactly the way I want it to, and
I have the report already set up. I use a print dialog
form to print the report, and I enter the date range I
want to print on the print dialog form.
Here's a portion of the code:
If (ReptToPrint = 6) Then
DoCmd.OpenReport "rptBonusEligibilityDetail",
acPreview, "", ("([Date]Between Forms![frmPrintReports]!
[txtBegDate] and Forms![frmPrintReports]![txtEndDate]) or
([Date] is Null) ")
End If

The problem arises when I specify the date range. I
included the is Null portion of the where clause so that I
would still be able to get all the employees' names on the
report, whether they've had an absence, tardy, etc. or
not. However, the way I have the clause worded, I get all
the employees with an Event within the date range
specified and those employees with NO Events at all. What
I want is to display ALL the employees, regardless of
whether they have any Events, but if they do have Events I
need to show ONLY the events for the date range I
specified.

I hope I've been able to make myself clearer.

Thanks so much (again)!
 
M

Marshall Barton

Gina said:
I'm sorry if I'm wearing out my welcome...I posted a
similar question before but was told I didn't provide
enough details.

I want a report that lists ALL of our employees and
whether they're eligible for a bonus in any given month.
Bonus eligibility is determined by the number of absences,
tardies, written warnings, and suspensions an employee has
had during the month. I want to the report to include ALL
the employees and their bonus eligibility to give to the
payroll department.

Here's the query the report is based on:
SELECT qryBonusEligReport.Event,
qryBonusEligReport.EventDate, Nz([CtUnexAbs]) AS
CountUnexAbs, Nz([CtTard]) AS CountTard, Nz([CtLtEarly])
AS CountLtEarly, Nz([CtWarn]) AS CountWarn, Date()-
[HireDate] AS DaysEmp, Nz([CtSusp]) AS CountSusp,
EmployeeData.EmployeeName, EmployeeData.HireDate,
EmployeeData.FileNumber, EmployeeData.Department,
EmployeeData.Status, EmployeeData.BonusGroup
FROM EmployeeData LEFT JOIN qryBonusEligReport ON
EmployeeData.EmployeeName =
qryBonusEligReport.EmployeeName;

I have two main tables:
tblEvents tblEmployeeData
FileNumber FileNumber
Event EmployeeName
EventDate Department
EventID HireDate
Status
EmployeeID

I have the query working exactly the way I want it to, and
I have the report already set up. I use a print dialog
form to print the report, and I enter the date range I
want to print on the print dialog form.
Here's a portion of the code:
If (ReptToPrint = 6) Then
DoCmd.OpenReport "rptBonusEligibilityDetail",
acPreview, "", ("([Date]Between Forms![frmPrintReports]!
[txtBegDate] and Forms![frmPrintReports]![txtEndDate]) or
([Date] is Null) ")
End If

The problem arises when I specify the date range. I
included the is Null portion of the where clause so that I
would still be able to get all the employees' names on the
report, whether they've had an absence, tardy, etc. or
not. However, the way I have the clause worded, I get all
the employees with an Event within the date range
specified and those employees with NO Events at all. What
I want is to display ALL the employees, regardless of
whether they have any Events, but if they do have Events I
need to show ONLY the events for the date range I
specified.
Click to expand...

I think the problem is that the query qryBonusEligReport
needs to use the date range criteria. The way you have it,
any person with no events in the date range, but with an
event outside the date range will be filtered out by the
date range criteria and the Is Null has no effect.

If you filter the qryBonusEligReport by the date range, then
people with no events will have one record with a Null
EventDate field. This way, there is no reason to use the Is
Null criteria.
 
G

Gina

Thank you, thank you, a million times thank you!
That's exactly the solution I needed!
I truly appreciate your help.

Thanks again,
Gina

-----Original Message-----
Gina said:
I'm sorry if I'm wearing out my welcome...I posted a
similar question before but was told I didn't provide
enough details.

I want a report that lists ALL of our employees and
whether they're eligible for a bonus in any given month.
Bonus eligibility is determined by the number of absences,
tardies, written warnings, and suspensions an employee has
had during the month. I want to the report to include ALL
the employees and their bonus eligibility to give to the
payroll department.

Here's the query the report is based on:
SELECT qryBonusEligReport.Event,
qryBonusEligReport.EventDate, Nz([CtUnexAbs]) AS
CountUnexAbs, Nz([CtTard]) AS CountTard, Nz([CtLtEarly])
AS CountLtEarly, Nz([CtWarn]) AS CountWarn, Date()-
[HireDate] AS DaysEmp, Nz([CtSusp]) AS CountSusp,
EmployeeData.EmployeeName, EmployeeData.HireDate,
EmployeeData.FileNumber, EmployeeData.Department,
EmployeeData.Status, EmployeeData.BonusGroup
FROM EmployeeData LEFT JOIN qryBonusEligReport ON
EmployeeData.EmployeeName =
qryBonusEligReport.EmployeeName;

I have two main tables:
tblEvents tblEmployeeData
FileNumber FileNumber
Event EmployeeName
EventDate Department
EventID HireDate
Status
EmployeeID

I have the query working exactly the way I want it to, and
I have the report already set up. I use a print dialog
form to print the report, and I enter the date range I
want to print on the print dialog form.
Here's a portion of the code:
If (ReptToPrint = 6) Then
DoCmd.OpenReport "rptBonusEligibilityDetail",
acPreview, "", ("([Date]Between Forms![frmPrintReports]!
[txtBegDate] and Forms![frmPrintReports]![txtEndDate]) or
([Date] is Null) ")
End If

The problem arises when I specify the date range. I
included the is Null portion of the where clause so that I
would still be able to get all the employees' names on the
report, whether they've had an absence, tardy, etc. or
not. However, the way I have the clause worded, I get all
the employees with an Event within the date range
specified and those employees with NO Events at all. What
I want is to display ALL the employees, regardless of
whether they have any Events, but if they do have Events I
need to show ONLY the events for the date range I
specified.
Click to expand...

I think the problem is that the query qryBonusEligReport
needs to use the date range criteria. The way you have it,
any person with no events in the date range, but with an
event outside the date range will be filtered out by the
date range criteria and the Is Null has no effect.

If you filter the qryBonusEligReport by the date range, then
people with no events will have one record with a Null
EventDate field. This way, there is no reason to use the Is
Null criteria.
Click to expand...
 
M

Marshall Barton

Gina said:
Thank you, thank you, a million times thank you!
That's exactly the solution I needed!
I truly appreciate your help.

Thanks again,
Gina
Click to expand...

You're Welcome (a million times? ;-)
Glad to be able to help.
--
Marsh
MVP [MS Access]

-----Original Message-----
Gina said:
I'm sorry if I'm wearing out my welcome...I posted a
similar question before but was told I didn't provide
enough details.

I want a report that lists ALL of our employees and
whether they're eligible for a bonus in any given month.
Bonus eligibility is determined by the number of absences,
tardies, written warnings, and suspensions an employee has
had during the month. I want to the report to include ALL
the employees and their bonus eligibility to give to the
payroll department.

Here's the query the report is based on:
SELECT qryBonusEligReport.Event,
qryBonusEligReport.EventDate, Nz([CtUnexAbs]) AS
CountUnexAbs, Nz([CtTard]) AS CountTard, Nz([CtLtEarly])
AS CountLtEarly, Nz([CtWarn]) AS CountWarn, Date()-
[HireDate] AS DaysEmp, Nz([CtSusp]) AS CountSusp,
EmployeeData.EmployeeName, EmployeeData.HireDate,
EmployeeData.FileNumber, EmployeeData.Department,
EmployeeData.Status, EmployeeData.BonusGroup
FROM EmployeeData LEFT JOIN qryBonusEligReport ON
EmployeeData.EmployeeName =
qryBonusEligReport.EmployeeName;

I have two main tables:
tblEvents tblEmployeeData
FileNumber FileNumber
Event EmployeeName
EventDate Department
EventID HireDate
Status
EmployeeID

I have the query working exactly the way I want it to, and
I have the report already set up. I use a print dialog
form to print the report, and I enter the date range I
want to print on the print dialog form.
Here's a portion of the code:
If (ReptToPrint = 6) Then
DoCmd.OpenReport "rptBonusEligibilityDetail",
acPreview, "", ("([Date]Between Forms![frmPrintReports]!
[txtBegDate] and Forms![frmPrintReports]![txtEndDate]) or
([Date] is Null) ")
End If

The problem arises when I specify the date range. I
included the is Null portion of the where clause so that I
would still be able to get all the employees' names on the
report, whether they've had an absence, tardy, etc. or
not. However, the way I have the clause worded, I get all
the employees with an Event within the date range
specified and those employees with NO Events at all. What
I want is to display ALL the employees, regardless of
whether they have any Events, but if they do have Events I
need to show ONLY the events for the date range I
specified.
Click to expand...
Click to expand...
Marshall said:
I think the problem is that the query qryBonusEligReport
needs to use the date range criteria. The way you have it,
any person with no events in the date range, but with an
event outside the date range will be filtered out by the
date range criteria and the Is Null has no effect.

If you filter the qryBonusEligReport by the date range, then
people with no events will have one record with a Null
EventDate field. This way, there is no reason to use the
Is Null criteria.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Range on report 2
print a date range from parameter query on a report header? 2
Subreport date range issue 2
Adding a Date Range to a Report 1
2 queries, a date range and a report?! 3
Date Range on Report 3
Displaying date parameter on a multiple page report 3
Report Date Range 2

Top