Date Criteria Correct?

[Linda RQ]

Hi,

Access 2000. I need to keep track of point totals for sick time for
employees for the last 12 weeks. I have my totals query totaling up
correctly. The glitch is, each employees time starts on the first date they
call in. Is this criteria below going to return the last 12 weeks from
today or the last 12 weeks for each employee?

=DateAdd("d",-84,Date())

Thanks,
Linda

 

[Ken Snell \(MVP\)]

Try it in Immediate Window and see --- It'll give you the date that is 12
weeks ago today. It will not be dependent upon an employee's "first date [he
calls] in".

 

[Linda RQ]

I tried a few tests and figured that out... Rats! If I want the point
totals to drop off 12 weeks ago based on the first date the employee calls
in, how could I do that?

Thanks,
Linda

Try it in Immediate Window and see --- It'll give you the date that is 12
weeks ago today. It will not be dependent upon an employee's "first date
[he calls] in".

--

Ken Snell
<MS ACCESS MVP>

Hi,

Access 2000. I need to keep track of point totals for sick time for
employees for the last 12 weeks. I have my totals query totaling up
correctly. The glitch is, each employees time starts on the first date
they call in. Is this criteria below going to return the last 12 weeks
from today or the last 12 weeks for each employee?



Thanks,
Linda

 

[Ken Snell \(MVP\)]

You want to use the first date on which the employee calls in as the
"starting date" for the DateAdd?

Well, how you do that depends on how your data are stored in the table. Tell
us the table's fields and what data each record holds, then we can suggest
how to "find" that first date value for the employee.
--

Ken Snell
<MS ACCESS MVP>

I tried a few tests and figured that out... Rats! If I want the point
totals to drop off 12 weeks ago based on the first date the employee calls
in, how could I do that?

Thanks,
Linda

Try it in Immediate Window and see --- It'll give you the date that is 12
weeks ago today. It will not be dependent upon an employee's "first date
[he calls] in".

--

Ken Snell
<MS ACCESS MVP>

Hi,

Access 2000. I need to keep track of point totals for sick time for
employees for the last 12 weeks. I have my totals query totaling up
correctly. The glitch is, each employees time starts on the first date
they call in. Is this criteria below going to return the last 12 weeks
from today or the last 12 weeks for each employee?


=DateAdd("d",-84,Date())

Thanks,
Linda

 

[Linda RQ]

tblUnscheduledTimeOff
UnscheduledTimeOffID
EmployeeID_fk
USTOTypeID_fk
USTODate

tblUnscheduledTimeOffTypes
UnscheduledTimeOffID
USTOPoints

I have a base query that calculates the unscheduled time off and this is
where I tried that DateAdd function, then I made a count of points query
based on that base query. I made a report based on the count query and it
shows the employees and the total points they have. I'm getting mixed up
because everyone would have a different start date to count from.

Thanks,
Linda

You want to use the first date on which the employee calls in as the
"starting date" for the DateAdd?

Well, how you do that depends on how your data are stored in the table.
Tell us the table's fields and what data each record holds, then we can
suggest how to "find" that first date value for the employee.
--

Ken Snell
<MS ACCESS MVP>

I tried a few tests and figured that out... Rats! If I want the point
totals to drop off 12 weeks ago based on the first date the employee calls
in, how could I do that?

Thanks,
Linda

Try it in Immediate Window and see --- It'll give you the date that is
12 weeks ago today. It will not be dependent upon an employee's "first
date [he calls] in".

--

Ken Snell
<MS ACCESS MVP>

Hi,

Access 2000. I need to keep track of point totals for sick time for
employees for the last 12 weeks. I have my totals query totaling up
correctly. The glitch is, each employees time starts on the first date
they call in. Is this criteria below going to return the last 12 weeks
from today or the last 12 weeks for each employee?


=DateAdd("d",-84,Date())

Thanks,
Linda

 

[Ken Snell \(MVP\)]

If I understand correctly, you'd want to use the earliest date in USTODate
field in the tblUnscheduledTimeOff table for a specific EmployeeID value; is
that right?

=DateAdd("d",-84,DMin("USTODate", "tblUnscheduledTimeOff", "EmployeeID=" &
PutEmployeeIDValueHere)

--

Ken Snell
<MS ACCESS MVP>

tblUnscheduledTimeOff
UnscheduledTimeOffID
EmployeeID_fk
USTOTypeID_fk
USTODate

tblUnscheduledTimeOffTypes
UnscheduledTimeOffID
USTOPoints

I have a base query that calculates the unscheduled time off and this is
where I tried that DateAdd function, then I made a count of points query
based on that base query. I made a report based on the count query and it
shows the employees and the total points they have. I'm getting mixed up
because everyone would have a different start date to count from.

Thanks,
Linda

You want to use the first date on which the employee calls in as the
"starting date" for the DateAdd?

Well, how you do that depends on how your data are stored in the table.
Tell us the table's fields and what data each record holds, then we can
suggest how to "find" that first date value for the employee.
--

Ken Snell
<MS ACCESS MVP>

I tried a few tests and figured that out... Rats! If I want the point
totals to drop off 12 weeks ago based on the first date the employee
calls in, how could I do that?

Thanks,
Linda

Try it in Immediate Window and see --- It'll give you the date that is
12 weeks ago today. It will not be dependent upon an employee's "first
date [he calls] in".

--

Ken Snell
<MS ACCESS MVP>

Hi,

Access 2000. I need to keep track of point totals for sick time for
employees for the last 12 weeks. I have my totals query totaling up
correctly. The glitch is, each employees time starts on the first
date they call in. Is this criteria below going to return the last 12
weeks from today or the last 12 weeks for each employee?


=DateAdd("d",-84,Date())

Thanks,
Linda

 

[Linda RQ]

Yes, that's right. I'll try this out tomorrow and look at the exact
language in the union contract book. Looking at this, would I need to make
my query so all the employees are listed on my report even if they don't
have a call in date? I think it's the join type I change? Right now, only
the employees that have call in dates show up on my query and only for the
last 84 days. If they called in on December 25th it doesn't show
up...however, if they called in on a holiday, they get 5 points instead of
1.5...sheesh, who ever makes up these rules sure doesn't think about how we
are going to keep track of this.

Linda
<TGF MS ACCESS MVPs>

If I understand correctly, you'd want to use the earliest date in USTODate
field in the tblUnscheduledTimeOff table for a specific EmployeeID value;
is that right?

=DateAdd("d",-84,DMin("USTODate", "tblUnscheduledTimeOff", "EmployeeID=" &
PutEmployeeIDValueHere)

--

Ken Snell
<MS ACCESS MVP>

tblUnscheduledTimeOff
UnscheduledTimeOffID
EmployeeID_fk
USTOTypeID_fk
USTODate

tblUnscheduledTimeOffTypes
UnscheduledTimeOffID
USTOPoints

I have a base query that calculates the unscheduled time off and this is
where I tried that DateAdd function, then I made a count of points query
based on that base query. I made a report based on the count query and
it shows the employees and the total points they have. I'm getting mixed
up because everyone would have a different start date to count from.

Thanks,
Linda

You want to use the first date on which the employee calls in as the
"starting date" for the DateAdd?

Well, how you do that depends on how your data are stored in the table.
Tell us the table's fields and what data each record holds, then we can
suggest how to "find" that first date value for the employee.
--

Ken Snell
<MS ACCESS MVP>



I tried a few tests and figured that out... Rats! If I want the point
totals to drop off 12 weeks ago based on the first date the employee
calls in, how could I do that?

Thanks,
Linda

Try it in Immediate Window and see --- It'll give you the date that is
12 weeks ago today. It will not be dependent upon an employee's "first
date [he calls] in".

--

Ken Snell
<MS ACCESS MVP>

Hi,

Access 2000. I need to keep track of point totals for sick time for
employees for the last 12 weeks. I have my totals query totaling up
correctly. The glitch is, each employees time starts on the first
date they call in. Is this criteria below going to return the last
12 weeks from today or the last 12 weeks for each employee?


=DateAdd("d",-84,Date())

Thanks,
Linda