dateadd criteria result is blank

  • Thread starter Thread starter nydia
  • Start date Start date
N

nydia

i have a qry with a termination date, i need my employee
to follow-up 30 days after termiantion date. i used the
dateadd function and when i run qry it's fine, gives me
the right dates, but when i put between[start]and[end] in
the criteria (dateadd field), and run it, i'm getting not
records. i've tried
exp1:datevalue(dateadd("d",30,[tblclient]!{termdate]))

i need the between[]and[]. so that my employee can run a
report on a weekly or monthly bases that will show, who's
follow-up date is due.

any help is greatly appreciated.
 
i have a qry with a termination date, i need my employee
to follow-up 30 days after termiantion date. i used the
dateadd function and when i run qry it's fine, gives me
the right dates, but when i put between[start]and[end] in
the criteria (dateadd field), and run it, i'm getting not
records. i've tried
exp1:datevalue(dateadd("d",30,[tblclient]!{termdate]))

i need the between[]and[]. so that my employee can run a
report on a weekly or monthly bases that will show, who's
follow-up date is due.

any help is greatly appreciated.
Click to expand...

The following expression will gather all records that have terminated
within a user selected period from the current date:

ToFollowUp: DateDiff("d",[TermDate],Date())

As criteria for this column:
Between 1 and CInt([How many days?])
 
i don't get it. the user will not open the qry at all.
they will open the report that will prompt them to enter
a start and end date. if we are in Jan, the user would
put in 1/1/04-01/31/04 and a list of all client who's
follow-up is in Jan. i'm a novice, so can u be more
specific
-----Original Message-----
i have a qry with a termination date, i need my employee
to follow-up 30 days after termiantion date. i used the
dateadd function and when i run qry it's fine, gives me
the right dates, but when i put between[start]and[end] in
the criteria (dateadd field), and run it, i'm getting not
records. i've tried
exp1:datevalue(dateadd("d",30,[tblclient]!{termdate]))

i need the between[]and[]. so that my employee can run a
report on a weekly or monthly bases that will show, who's
follow-up date is due.

any help is greatly appreciated.
Click to expand...

The following expression will gather all records that have terminated
within a user selected period from the current date:

ToFollowUp: DateDiff("d",[TermDate],Date())

As criteria for this column:
Between 1 and CInt([How many days?])
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
i'm not really sure what you mean. i have a termination
date and i need to the user to be able to print a report
each month that shows whos follow-up is coming up that
month. there are 4 follow-ups per client 1,3,6 and 12
moonths from the termination date
-----Original Message-----
i have a qry with a termination date, i need my employee
to follow-up 30 days after termiantion date. i used the
dateadd function and when i run qry it's fine, gives me
the right dates, but when i put between[start]and[end] in
the criteria (dateadd field), and run it, i'm getting not
records. i've tried
exp1:datevalue(dateadd("d",30,[tblclient]!{termdate]))

i need the between[]and[]. so that my employee can run a
report on a weekly or monthly bases that will show, who's
follow-up date is due.

any help is greatly appreciated.
Click to expand...

The following expression will gather all records that have terminated
within a user selected period from the current date:

ToFollowUp: DateDiff("d",[TermDate],Date())

As criteria for this column:
Between 1 and CInt([How many days?])
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Hi Nydia,

Maybe a simple example may help?

tblPersonnel

PayrollID EmpName TermDate
1 Homer
2 Marge 11/17/2003
3 Bart 12/17/2003
4 Lisa 1/16/2003

TermDate is type Date/Time...

If we run the following query

SELECT
PayrollID,
EmpName,
TermDate,
DateValue(DateAdd("d",30,[TermDate])) AS 30Days,
DateValue(DateAdd("d",60,[TermDate])) AS 60Days,
DateValue(DateAdd("d",90,[TermDate])) AS 90Days
FROM tblPersonnel;

we get:

PayrollID EmpName TermDate 30Days 60Days 90Days
1 Homer #error #error #error
2 Marge 11/17/2003 12/17/2003 1/16/2004 2/15/2004
3 Bart 12/17/2003 1/16/2004 2/15/2004 3/16/2004
4 Lisa 1/16/2003 2/15/2003 3/17/2003 4/16/2003

So..the first thing we notice is that we did not really want
the employee records for employees who have not been
terminated. So we set a criteria under TermDate to weed
those out (in our case -- Homer).

SELECT
PayrollID,
EmpName,
TermDate,
DateValue(DateAdd("d",30,[TermDate])) AS 30Days,
DateValue(DateAdd("d",60,[TermDate])) AS 60Days,
DateValue(DateAdd("d",90,[TermDate])) AS 90Days
FROM tblPersonnel
WHERE (([TermDate] Is Not Null));

giving us

PayrollID EmpName TermDate 30Days 60Days 90Days
2 Marge 11/17/2003 12/17/2003 1/16/2004 2/15/2004
3 Bart 12/17/2003 1/16/2004 2/15/2004 3/16/2004
4 Lisa 1/16/2003 2/15/2003 3/17/2003 4/16/2003

ok..all so far has been straight forward and it sounds
like you have successfully done most of this already.

To select a period of time for a 30-day termination
anniversary, in the Criteria row under "30Days," we enter

BETWEEN [Enter Start Date] AND [Enter End Date]

For the 2 columns of your query grid that have a criteria:

Field: TermDate 30Days: DateValue(DateAdd("d",30,[TermDate]))
Table: tblPersonnel
Sort:
Show:
Criteria: IS NOT NULL BETWEEN [Enter Start Date] AND [Enter End Date]

your query would look like this:

SELECT
PayrollID,
EmpName,
TermDate,
DateValue(DateAdd("d",30,[TermDate])) AS 30Days,
DateValue(DateAdd("d",60,[TermDate])) AS 60Days,
DateValue(DateAdd("d",90,[TermDate])) AS 90Days
FROM tblPersonnel
WHERE
(([TermDate] Is Not Null)
AND
((DateValue(DateAdd("d",30,[TermDate])))
Between [Enter Start Date] And [Enter End Date]));

If we save this query, and run it to find
all employees who have a 30-day termination
anniversary in Feb 04...

for [Enter Start Date], we enter
2/1/04
for [Enter End Date], we enter
2/29/04

and get following result:


PayrollID EmpName TermDate 30Days 60Days 90Days
4 Lisa 1/16/2003 2/15/2003 3/17/2003 4/16/2003

Only Lisa has a 30-day termination anniversary in Feb 04.

Hopefully an example will help.

Good luck,

Gary Walter

nydia said:
i have a qry with a termination date, i need my employee
to follow-up 30 days after termiantion date. i used the
dateadd function and when i run qry it's fine, gives me
the right dates, but when i put between[start]and[end] in
the criteria (dateadd field), and run it, i'm getting not
records. i've tried
exp1:datevalue(dateadd("d",30,[tblclient]!{termdate]))

i need the between[]and[]. so that my employee can run a
report on a weekly or monthly bases that will show, who's
follow-up date is due.

any help is greatly appreciated.
Click to expand...
 
Back
Top