query for a renewal date again

query for a renewal date again

Post by TXJ1dG » Tue, 16 Aug 2005 23:36:14


Sorry I hit enter and it posted a blank question.
OK I have a Access 2002 database that contains a tabele called "Current
Members" with fields called "Active"(yes or no, check box) and a field called
"experation date" (full date ie: 1/1/2005) and email address(text, saved as a
hyperlink mailto:)

I need to *** y the table for 1, are they active? and two the experation
date 60 days before the experation.

As an example the current experation date for some of the records is between
say 11/1/2005 and 12/1/2005. Results if met would be records I need to send a
reminder out for in September and October.

I'd like to return any record 60 days prior to those dates.
Is this doable or should I look at it from a different perspective to obtain
the same result?

Possibly for another forum I then want to add the email field on the
returned records to the BCC line in an Outlook e mail.



--
Mike Ruth
 
 
 

query for a renewal date again

Post by Allen Brow » Tue, 16 Aug 2005 23:45:48

You can create a query into this table.

In the Criteria row under the Active field, enter:
True

In the Criteria row under the date field, enter:
<= DateAdd("d", 60, Date())

Note that this is not an optimal structure. I suspect you need a related
table of membership details, where a record is added each time the member
pays their dues. Then next time they are due would then be calculated from
their most recent date in this table by using a Totals query (Totals on View
menu in query design.)

Use SendObject to send the email. There's an argument for the BCC.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.

 
 
 

query for a renewal date again

Post by TXJ1dG » Wed, 17 Aug 2005 00:07:06

I do believe this is going to work for me, Thank you.
--
Mike Ruth
 
 
 

query for a renewal date again

Post by TXJ1dG » Thu, 01 Sep 2005 12:47:10

Well I'm going to need a little more help on getting the e mail field into
Outlook.
What I did is create a macro that runs the query, the results of the query
have a field called emailaddress.
Using the "sendto" funtion I want the returned emailaddress's to be placed
in the BCC field. The balance of the email should be blank, of which I will
fill in the body.
I can't seem to enter a statement that will perform that.
Thanks in Advance



--
Mike Ruth
 
 
 

query for a renewal date again

Post by Allen Brow » Thu, 01 Sep 2005 13:07:04

he code will look something like this:

Dim varEmail As Variant
varEmail = DLookup("emailaddress", "query1")
DoCmd.SendObject acSendQuery, "Query1",,,,varEmail,,,True

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mruth" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...