Sorry about the vagueness of my question...
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
The purpose of the report is to list ALL 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. 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'm looking for is no Events within that date range.
I hope I've been able to make myself clearer.
Thanks so much.
-----Original Message-----
Without knowing what data you have, and how you have it arranged into
tables, it's really going to be difficult to answer your question. If your
data is properly structured, you can likely do what you want, but we need
you to clarify.
Larry Linson
Microsoft Access MVP
Gina said:
Hi!
Can someone tell me if it is possible to return records
with a null value within a specified date range?
I want to list employees that have an absence within a
certain date range (any given month, let's say 09/01/03 to
09/30/03), and ALSO those employees with no absence within
that specified date range. I tried using this :
("([EventDate]Between Forms![frmPrintReports]! [txtBegDate]
and Forms![frmPrintReports]![txtEndDate]) or ([EventDate]
is Null) ")
but that gives me only the employees that have absolutely
no absences.
Is it possible to combine the "is Null" and "Between" to
get the results I'm looking for? (Or does someone have a
better suggestion?)
Thanks.
.
