Annual reminder query

[Bruce]

This is something of a repost, but I could not figure out how to implement
the suggestions received at the time, or they did not work.
I have been working on something for a while, and I can't seem to get it on
track. I will describe it as succinctly as is possible, and can add more
information as needed.
Some or our vendors need to be certified to various standards (such as ISO).
The certificates include an expiration date, usually three or so years from
the present. I have set up a query (as the basis for a report) so that as
the expiration date nears we generate a notice requesting an updated
certificate. If we don't receive an update we send a second notice, etc.
A new requirement calls for us also to obtain annually from each vendor a
statement that they are still compliant with the certification standards. To
that end I need to send out a notice requesting the statement. It would be
best if I could do so on about the expiration date. As the database is not,
if a company's certificate is due to expire on 12/1/04, on 11/1/04 I send a
notice that the certificate is about to expire. That part is OK. If another
company's certificate expires on 12/1/05 or 12/1/06, on 11/1/04 (one month
before the expiration month and day) I want to send a notice requesting a
statement of continued compliance. This is where I am having difficulty.
First, these are the relevant fields, I think, in tblVendors:
VendorID (PK) - Autonumber
VendorName - Text
ExpDate - Date/Time (certificate expiration date)

There are other fields such as city, state, fax number, etc., but I don't
think they are relevant to the current task.
I am not sure of the best approach, which is why I am writing. I tried
filtering the query with the criteria >DateSerial(Year(Date())+1,1,0) in
ExpDate (to exclude vendors with an expiration date that is this year, since
they are handled differently), then creating a calculated field in qryVendors
(based on tblVendors):
ReviewDate: DateSerial(Year(Date()),Month([ExpDate]),Day([ExpDate])), then
applying criteria using DateAdd to the results of that field as if it was the
current year (12/1/06 will produce 12/1/04 in ReviewDate), but I get a type
mismatch error message.
I could go through a list of things I have tried, but the point is that none
of them have worked. Any suggestions of how I can identify vendors with
certificates that are due to expire one year plus one month (or less) from
now, two years plus one month (or less) from now, etc.?
By the way, filtering out vendors with expiration dates that occur in the
current year seems to generate a new set of problems at the edges of the
year. For instance, if a certificate expires on 12/30/05, if they are late I
can't send out a second notice, since it will be the current year by then and
they will be filtered out.
Somewhere along the line I need to add a yes/no field to identify vendors
from whom information has been requested but not yet received, but I'm not
sure that fits into any of what I have already asked.
As I said, I can provide more information, but I am trying to keep it as
simple as I can. The details are not really relevant, and once I get the
basic principle to work the rest will be OK.

 

[martin]

SELECT Patient.[end date], Patient.*
FROM Patient
WHERE (((Patient.[end date]) Is Not Null And (Patient.[end date])>Date()-30));

The above sql will show all patients(Vendors) with an end date(exp date)
that are greater then 30 days ago. The following where will show last year

WHERE (((Patient.[end date]) Is Not Null And (Patient.[end
date])>Dateserial(year(date())-1,month(date()),day(date()))));

This will show between today and 30 days ago

WHERE (((Patient.[end date]) Is Not Null And (Patient.[end date]) Between
Date() And Date()-30));

Hope this Helps
Martin

This is something of a repost, but I could not figure out how to implement
the suggestions received at the time, or they did not work.
I have been working on something for a while, and I can't seem to get it on
track. I will describe it as succinctly as is possible, and can add more
information as needed.
Some or our vendors need to be certified to various standards (such as ISO).
The certificates include an expiration date, usually three or so years from
the present. I have set up a query (as the basis for a report) so that as
the expiration date nears we generate a notice requesting an updated
certificate. If we don't receive an update we send a second notice, etc.
A new requirement calls for us also to obtain annually from each vendor a
statement that they are still compliant with the certification standards. To
that end I need to send out a notice requesting the statement. It would be
best if I could do so on about the expiration date. As the database is not,
if a company's certificate is due to expire on 12/1/04, on 11/1/04 I send a
notice that the certificate is about to expire. That part is OK. If another
company's certificate expires on 12/1/05 or 12/1/06, on 11/1/04 (one month
before the expiration month and day) I want to send a notice requesting a
statement of continued compliance. This is where I am having difficulty.
First, these are the relevant fields, I think, in tblVendors:
VendorID (PK) - Autonumber
VendorName - Text
ExpDate - Date/Time (certificate expiration date)

There are other fields such as city, state, fax number, etc., but I don't
think they are relevant to the current task.
I am not sure of the best approach, which is why I am writing. I tried
filtering the query with the criteria >DateSerial(Year(Date())+1,1,0) in
ExpDate (to exclude vendors with an expiration date that is this year, since
they are handled differently), then creating a calculated field in qryVendors
(based on tblVendors):
ReviewDate: DateSerial(Year(Date()),Month([ExpDate]),Day([ExpDate])), then
applying criteria using DateAdd to the results of that field as if it was the
current year (12/1/06 will produce 12/1/04 in ReviewDate), but I get a type
mismatch error message.
I could go through a list of things I have tried, but the point is that none
of them have worked. Any suggestions of how I can identify vendors with
certificates that are due to expire one year plus one month (or less) from
now, two years plus one month (or less) from now, etc.?
By the way, filtering out vendors with expiration dates that occur in the
current year seems to generate a new set of problems at the edges of the
year. For instance, if a certificate expires on 12/30/05, if they are late I
can't send out a second notice, since it will be the current year by then and
they will be filtered out.
Somewhere along the line I need to add a yes/no field to identify vendors
from whom information has been requested but not yet received, but I'm not
sure that fits into any of what I have already asked.
As I said, I can provide more information, but I am trying to keep it as
simple as I can. The details are not really relevant, and once I get the
basic principle to work the rest will be OK.

 

[Bruce]

If a vendor's certificate expires on 12/1/05, I want to send a request on
11/1/04 for a statement of continued compliance with the terms of the
certificate. If it expires on 12/1/04, I want to sent a request on 11/1/04
for an updated certificate. If it expires on June 1 of any year, I don't
want to see it until May 1 (one month before action is needed).
My intention is to use the query as the source for a report. The report is
a fax that will be printed and sent to the vendor. While I can group the
reports by expiration date and then select just the ones that are within 30
days, I would prefer to automate the process. Filtering out those who are to
expire in 30 days or whatever is not a problem. The problem is how on
November 1 of this year I can obtain a listing of vendors whose certificate
is due to expire on December 1 or sooner in 2005, 2006, 2007, etc.

SELECT Patient.[end date], Patient.*
FROM Patient
WHERE (((Patient.[end date]) Is Not Null And (Patient.[end date])>Date()-30));

The above sql will show all patients(Vendors) with an end date(exp date)
that are greater then 30 days ago. The following where will show last year

WHERE (((Patient.[end date]) Is Not Null And (Patient.[end
date])>Dateserial(year(date())-1,month(date()),day(date()))));

This will show between today and 30 days ago

WHERE (((Patient.[end date]) Is Not Null And (Patient.[end date]) Between
Date() And Date()-30));

Hope this Helps
Martin

This is something of a repost, but I could not figure out how to implement
the suggestions received at the time, or they did not work.
I have been working on something for a while, and I can't seem to get it on
track. I will describe it as succinctly as is possible, and can add more
information as needed.
Some or our vendors need to be certified to various standards (such as ISO).
The certificates include an expiration date, usually three or so years from
the present. I have set up a query (as the basis for a report) so that as
the expiration date nears we generate a notice requesting an updated
certificate. If we don't receive an update we send a second notice, etc.
A new requirement calls for us also to obtain annually from each vendor a
statement that they are still compliant with the certification standards. To
that end I need to send out a notice requesting the statement. It would be
best if I could do so on about the expiration date. As the database is not,
if a company's certificate is due to expire on 12/1/04, on 11/1/04 I send a
notice that the certificate is about to expire. That part is OK. If another
company's certificate expires on 12/1/05 or 12/1/06, on 11/1/04 (one month
before the expiration month and day) I want to send a notice requesting a
statement of continued compliance. This is where I am having difficulty.
First, these are the relevant fields, I think, in tblVendors:
VendorID (PK) - Autonumber
VendorName - Text
ExpDate - Date/Time (certificate expiration date)

There are other fields such as city, state, fax number, etc., but I don't
think they are relevant to the current task.
I am not sure of the best approach, which is why I am writing. I tried
filtering the query with the criteria >DateSerial(Year(Date())+1,1,0) in
ExpDate (to exclude vendors with an expiration date that is this year, since
they are handled differently), then creating a calculated field in qryVendors
(based on tblVendors):
ReviewDate: DateSerial(Year(Date()),Month([ExpDate]),Day([ExpDate])), then
applying criteria using DateAdd to the results of that field as if it was the
current year (12/1/06 will produce 12/1/04 in ReviewDate), but I get a type
mismatch error message.
I could go through a list of things I have tried, but the point is that none
of them have worked. Any suggestions of how I can identify vendors with
certificates that are due to expire one year plus one month (or less) from
now, two years plus one month (or less) from now, etc.?
By the way, filtering out vendors with expiration dates that occur in the
current year seems to generate a new set of problems at the edges of the
year. For instance, if a certificate expires on 12/30/05, if they are late I
can't send out a second notice, since it will be the current year by then and
they will be filtered out.
Somewhere along the line I need to add a yes/no field to identify vendors
from whom information has been requested but not yet received, but I'm not
sure that fits into any of what I have already asked.
As I said, I can provide more information, but I am trying to keep it as
simple as I can. The details are not really relevant, and once I get the
basic principle to work the rest will be OK.

 

[martin]

SELECT Patient.*
FROM Patient
WHERE (((Patient.[end date]) Between
DateSerial(Year(Date())+1,Month(Date())+2,1) And
DateSerial(Year(Date())+1,Month(Date())+3,1)-1));

This will do december of next year. The statement "month(date())+2" says
this month 10 plus 2 =12 which is december the day part has 1 so the first
date is dec 1 2005 the second part of the statement say "month(date())+3"
which means january 2006 with day at 1 if you minus 1 you get dec 31 2005
which means you have between dec 1 2005 and dec 31 2005. So now all you have
to change is the year if you want later than next year or the month if you
need only want next month it would be +1 and +2 not +2 and +3 as done here.

HTH
Martin

If a vendor's certificate expires on 12/1/05, I want to send a request on
11/1/04 for a statement of continued compliance with the terms of the
certificate. If it expires on 12/1/04, I want to sent a request on 11/1/04
for an updated certificate. If it expires on June 1 of any year, I don't
want to see it until May 1 (one month before action is needed).
My intention is to use the query as the source for a report. The report is
a fax that will be printed and sent to the vendor. While I can group the
reports by expiration date and then select just the ones that are within 30
days, I would prefer to automate the process. Filtering out those who are to
expire in 30 days or whatever is not a problem. The problem is how on
November 1 of this year I can obtain a listing of vendors whose certificate
is due to expire on December 1 or sooner in 2005, 2006, 2007, etc.

SELECT Patient.[end date], Patient.*
FROM Patient
WHERE (((Patient.[end date]) Is Not Null And (Patient.[end date])>Date()-30));

The above sql will show all patients(Vendors) with an end date(exp date)
that are greater then 30 days ago. The following where will show last year

WHERE (((Patient.[end date]) Is Not Null And (Patient.[end
date])>Dateserial(year(date())-1,month(date()),day(date()))));

This will show between today and 30 days ago

WHERE (((Patient.[end date]) Is Not Null And (Patient.[end date]) Between
Date() And Date()-30));

Hope this Helps
Martin

This is something of a repost, but I could not figure out how to implement
the suggestions received at the time, or they did not work.
I have been working on something for a while, and I can't seem to get it on
track. I will describe it as succinctly as is possible, and can add more
information as needed.
Some or our vendors need to be certified to various standards (such as ISO).
The certificates include an expiration date, usually three or so years from
the present. I have set up a query (as the basis for a report) so that as
the expiration date nears we generate a notice requesting an updated
certificate. If we don't receive an update we send a second notice, etc.
A new requirement calls for us also to obtain annually from each vendor a
statement that they are still compliant with the certification standards. To
that end I need to send out a notice requesting the statement. It would be
best if I could do so on about the expiration date. As the database is not,
if a company's certificate is due to expire on 12/1/04, on 11/1/04 I send a
notice that the certificate is about to expire. That part is OK. If another
company's certificate expires on 12/1/05 or 12/1/06, on 11/1/04 (one month
before the expiration month and day) I want to send a notice requesting a
statement of continued compliance. This is where I am having difficulty.
First, these are the relevant fields, I think, in tblVendors:
VendorID (PK) - Autonumber
VendorName - Text
ExpDate - Date/Time (certificate expiration date)

There are other fields such as city, state, fax number, etc., but I don't
think they are relevant to the current task.
I am not sure of the best approach, which is why I am writing. I tried
filtering the query with the criteria >DateSerial(Year(Date())+1,1,0) in
ExpDate (to exclude vendors with an expiration date that is this year, since
they are handled differently), then creating a calculated field in qryVendors
(based on tblVendors):
ReviewDate: DateSerial(Year(Date()),Month([ExpDate]),Day([ExpDate])), then
applying criteria using DateAdd to the results of that field as if it was the
current year (12/1/06 will produce 12/1/04 in ReviewDate), but I get a type
mismatch error message.
I could go through a list of things I have tried, but the point is that none
of them have worked. Any suggestions of how I can identify vendors with
certificates that are due to expire one year plus one month (or less) from
now, two years plus one month (or less) from now, etc.?
By the way, filtering out vendors with expiration dates that occur in the
current year seems to generate a new set of problems at the edges of the
year. For instance, if a certificate expires on 12/30/05, if they are late I
can't send out a second notice, since it will be the current year by then and
they will be filtered out.
Somewhere along the line I need to add a yes/no field to identify vendors
from whom information has been requested but not yet received, but I'm not
sure that fits into any of what I have already asked.
As I said, I can provide more information, but I am trying to keep it as
simple as I can. The details are not really relevant, and once I get the
basic principle to work the rest will be OK.

 

[martin]

One more thing If you want more than 1 year in your list use the or statement
in your sql where clause like this

WHERE (((Patient.[end date]) Between
DateSerial(Year(Date())+1,Month(Date())+2,1) And
DateSerial(Year(Date())+1,Month(Date())+3,1)-1)) OR (((Patient.[end date])
Between DateSerial(Year(Date())+2,Month(Date())+2,1) And
DateSerial(Year(Date())+2,Month(Date())+3,1)-1));

This will have dec 2005 and dec 2006.

HTH
Martin

SELECT Patient.*
FROM Patient
WHERE (((Patient.[end date]) Between
DateSerial(Year(Date())+1,Month(Date())+2,1) And
DateSerial(Year(Date())+1,Month(Date())+3,1)-1));

This will do december of next year. The statement "month(date())+2" says
this month 10 plus 2 =12 which is december the day part has 1 so the first
date is dec 1 2005 the second part of the statement say "month(date())+3"
which means january 2006 with day at 1 if you minus 1 you get dec 31 2005
which means you have between dec 1 2005 and dec 31 2005. So now all you have
to change is the year if you want later than next year or the month if you
need only want next month it would be +1 and +2 not +2 and +3 as done here.

HTH
Martin

If a vendor's certificate expires on 12/1/05, I want to send a request on
11/1/04 for a statement of continued compliance with the terms of the
certificate. If it expires on 12/1/04, I want to sent a request on 11/1/04
for an updated certificate. If it expires on June 1 of any year, I don't
want to see it until May 1 (one month before action is needed).
My intention is to use the query as the source for a report. The report is
a fax that will be printed and sent to the vendor. While I can group the
reports by expiration date and then select just the ones that are within 30
days, I would prefer to automate the process. Filtering out those who are to
expire in 30 days or whatever is not a problem. The problem is how on
November 1 of this year I can obtain a listing of vendors whose certificate
is due to expire on December 1 or sooner in 2005, 2006, 2007, etc.

SELECT Patient.[end date], Patient.*
FROM Patient
WHERE (((Patient.[end date]) Is Not Null And (Patient.[end date])>Date()-30));

The above sql will show all patients(Vendors) with an end date(exp date)
that are greater then 30 days ago. The following where will show last year

WHERE (((Patient.[end date]) Is Not Null And (Patient.[end
date])>Dateserial(year(date())-1,month(date()),day(date()))));

This will show between today and 30 days ago

WHERE (((Patient.[end date]) Is Not Null And (Patient.[end date]) Between
Date() And Date()-30));

Hope this Helps
Martin

:

This is something of a repost, but I could not figure out how to implement
the suggestions received at the time, or they did not work.
I have been working on something for a while, and I can't seem to get it on
track. I will describe it as succinctly as is possible, and can add more
information as needed.
Some or our vendors need to be certified to various standards (such as ISO).
The certificates include an expiration date, usually three or so years from
the present. I have set up a query (as the basis for a report) so that as
the expiration date nears we generate a notice requesting an updated
certificate. If we don't receive an update we send a second notice, etc.
A new requirement calls for us also to obtain annually from each vendor a
statement that they are still compliant with the certification standards. To
that end I need to send out a notice requesting the statement. It would be
best if I could do so on about the expiration date. As the database is not,
if a company's certificate is due to expire on 12/1/04, on 11/1/04 I send a
notice that the certificate is about to expire. That part is OK. If another
company's certificate expires on 12/1/05 or 12/1/06, on 11/1/04 (one month
before the expiration month and day) I want to send a notice requesting a
statement of continued compliance. This is where I am having difficulty.
First, these are the relevant fields, I think, in tblVendors:
VendorID (PK) - Autonumber
VendorName - Text
ExpDate - Date/Time (certificate expiration date)

There are other fields such as city, state, fax number, etc., but I don't
think they are relevant to the current task.
I am not sure of the best approach, which is why I am writing. I tried
filtering the query with the criteria >DateSerial(Year(Date())+1,1,0) in
ExpDate (to exclude vendors with an expiration date that is this year, since
they are handled differently), then creating a calculated field in qryVendors
(based on tblVendors):
ReviewDate: DateSerial(Year(Date()),Month([ExpDate]),Day([ExpDate])), then
applying criteria using DateAdd to the results of that field as if it was the
current year (12/1/06 will produce 12/1/04 in ReviewDate), but I get a type
mismatch error message.
I could go through a list of things I have tried, but the point is that none
of them have worked. Any suggestions of how I can identify vendors with
certificates that are due to expire one year plus one month (or less) from
now, two years plus one month (or less) from now, etc.?
By the way, filtering out vendors with expiration dates that occur in the
current year seems to generate a new set of problems at the edges of the
year. For instance, if a certificate expires on 12/30/05, if they are late I
can't send out a second notice, since it will be the current year by then and
they will be filtered out.
Somewhere along the line I need to add a yes/no field to identify vendors
from whom information has been requested but not yet received, but I'm not
sure that fits into any of what I have already asked.
As I said, I can provide more information, but I am trying to keep it as
simple as I can. The details are not really relevant, and once I get the
basic principle to work the rest will be OK.

 

[Bruce]

Thanks for the suggestion. I see where you are going with that, and I think
it could be made to work for me. I do not actually need to do this on the
first of the month (I just used that as an example), but I think I see how to
modify the statement to accomodate any day of the month. I suppose I could
string together the various SQL statements (for next year, two years from
now, etc.). I would rather not have to use multiple queries.
Thanks again for the time and thought you have put into this.

SELECT Patient.*
FROM Patient
WHERE (((Patient.[end date]) Between
DateSerial(Year(Date())+1,Month(Date())+2,1) And
DateSerial(Year(Date())+1,Month(Date())+3,1)-1));

This will do december of next year. The statement "month(date())+2" says
this month 10 plus 2 =12 which is december the day part has 1 so the first
date is dec 1 2005 the second part of the statement say "month(date())+3"
which means january 2006 with day at 1 if you minus 1 you get dec 31 2005
which means you have between dec 1 2005 and dec 31 2005. So now all you have
to change is the year if you want later than next year or the month if you
need only want next month it would be +1 and +2 not +2 and +3 as done here.

HTH
Martin

If a vendor's certificate expires on 12/1/05, I want to send a request on
11/1/04 for a statement of continued compliance with the terms of the
certificate. If it expires on 12/1/04, I want to sent a request on 11/1/04
for an updated certificate. If it expires on June 1 of any year, I don't
want to see it until May 1 (one month before action is needed).
My intention is to use the query as the source for a report. The report is
a fax that will be printed and sent to the vendor. While I can group the
reports by expiration date and then select just the ones that are within 30
days, I would prefer to automate the process. Filtering out those who are to
expire in 30 days or whatever is not a problem. The problem is how on
November 1 of this year I can obtain a listing of vendors whose certificate
is due to expire on December 1 or sooner in 2005, 2006, 2007, etc.

SELECT Patient.[end date], Patient.*
FROM Patient
WHERE (((Patient.[end date]) Is Not Null And (Patient.[end date])>Date()-30));

The above sql will show all patients(Vendors) with an end date(exp date)
that are greater then 30 days ago. The following where will show last year

WHERE (((Patient.[end date]) Is Not Null And (Patient.[end
date])>Dateserial(year(date())-1,month(date()),day(date()))));

This will show between today and 30 days ago

WHERE (((Patient.[end date]) Is Not Null And (Patient.[end date]) Between
Date() And Date()-30));

Hope this Helps
Martin

:

This is something of a repost, but I could not figure out how to implement
the suggestions received at the time, or they did not work.
I have been working on something for a while, and I can't seem to get it on
track. I will describe it as succinctly as is possible, and can add more
information as needed.
Some or our vendors need to be certified to various standards (such as ISO).
The certificates include an expiration date, usually three or so years from
the present. I have set up a query (as the basis for a report) so that as
the expiration date nears we generate a notice requesting an updated
certificate. If we don't receive an update we send a second notice, etc.
A new requirement calls for us also to obtain annually from each vendor a
statement that they are still compliant with the certification standards. To
that end I need to send out a notice requesting the statement. It would be
best if I could do so on about the expiration date. As the database is not,
if a company's certificate is due to expire on 12/1/04, on 11/1/04 I send a
notice that the certificate is about to expire. That part is OK. If another
company's certificate expires on 12/1/05 or 12/1/06, on 11/1/04 (one month
before the expiration month and day) I want to send a notice requesting a
statement of continued compliance. This is where I am having difficulty.
First, these are the relevant fields, I think, in tblVendors:
VendorID (PK) - Autonumber
VendorName - Text
ExpDate - Date/Time (certificate expiration date)

There are other fields such as city, state, fax number, etc., but I don't
think they are relevant to the current task.
I am not sure of the best approach, which is why I am writing. I tried
filtering the query with the criteria >DateSerial(Year(Date())+1,1,0) in
ExpDate (to exclude vendors with an expiration date that is this year, since
they are handled differently), then creating a calculated field in qryVendors
(based on tblVendors):
ReviewDate: DateSerial(Year(Date()),Month([ExpDate]),Day([ExpDate])), then
applying criteria using DateAdd to the results of that field as if it was the
current year (12/1/06 will produce 12/1/04 in ReviewDate), but I get a type
mismatch error message.
I could go through a list of things I have tried, but the point is that none
of them have worked. Any suggestions of how I can identify vendors with
certificates that are due to expire one year plus one month (or less) from
now, two years plus one month (or less) from now, etc.?
By the way, filtering out vendors with expiration dates that occur in the
current year seems to generate a new set of problems at the edges of the
year. For instance, if a certificate expires on 12/30/05, if they are late I
can't send out a second notice, since it will be the current year by then and
they will be filtered out.
Somewhere along the line I need to add a yes/no field to identify vendors
from whom information has been requested but not yet received, but I'm not
sure that fits into any of what I have already asked.
As I said, I can provide more information, but I am trying to keep it as
simple as I can. The details are not really relevant, and once I get the
basic principle to work the rest will be OK.