Birthday and last contact date reminder

[hisham84]

I'm building a database to store client information and I would like to be able to generate monthly reports that specifies clients for me to contact. I want the report to show clients with an upcoming birthday (within 30 days), clients whom I haven't contacted in over 60 days (i have a date field in the client table for the last contact date) and clients with contracts due to expire within 4 months (i have a seperate table where I have an expiry date field).

I've got background developing databases but i'm a little rusty since it's been a while. I know how to input/hide/show fields in the query but I need help with the expressions. Any ideas on how I should word them?

Much appreciated.

 

[hisham84]

my client table is called clienttable

assume have 3 fields in there:

clientid
clientname
clientdob -> short date field

i want the query to only show me clientname and clientdob (no client ID) for clients with a birthday coming up in 30 days.

I've searched this forum but none of the solutions work for me... i'm using access 2007. Please help!

Thanks!

 

[khawar]

You have to add some more fields to your table such as lastcontact date and contract expiry date

 

[hisham84]

You have to add some more fields to your table such as lastcontact date and contract expiry date

Thanks for the response. I forgot to mention that I already have those fields in my tables. I'm pretty sure I've got all the necessary fields. What i'm looking for is for someone to help with the actual code/expression that will go in the query.

I want it to pull a clients records where lastcontactdate is > 60 days in the past or if their contract expiry date is less than 4 months away or if their birthday is les than 14 days away.

I know how to tell the query which fields to show if a record matches up, my only struggle is with the code... date of birth shows dates as 03/24/1984 and I want it to show the record if i run the query and todays date is 04/01/2009 (just an example, as i would like it to show it as early as 2 weeks before the birthday).

Thanks,

 

[khawar]

upload some sample data if possible

 

[hisham84]

here's some sample data:

tblclient
- id (primary key)
- name
- dob
- phone
- address
- email
- lstcontact

tbltrans
- transid (primary key)
- id (foreign key from tblclient)
- servicedescription
- servicetype
- price
- expirydate

(there's more fields in each table, this is only a sample)

I want my query to show

- name (tblclient)
- phone (tblclient)
- email (tblclient)
- dob (tblclient)
- lastcontact (tblclient)
- servicedescription (tbltrans)
- expirydate (tbltrans)

the criteria for records to show, just as mentioned before, is: birthday in 14 days or less, last contact date greater than 60 days in the past, expiry date of 4 months or less into the future.

Thanks for your help

 

[raskew]

Hi -

For illustrative purposes, I've put all relevant items in one table:

SELECT
    tblTest.clientName
  , tblTest.DOB
  , tblTest.LastContact
  , tblTest.ExpiryDate
FROM
   tblTest
WHERE
   (((DateDiff("d",Date(),[COLOR="Red"]Anniversary[/COLOR]([DOB],True)))<=30)) 
OR
   (((DateDiff("d",[LastContact],Date()))>=60)) 
OR
   (((DateDiff("m",Date(),[ExpiryDate]))<=4));

Function Anniversary() below, returns the next anniversary of an event (birthday, whatever) whether it's in the current or next year. Copy/paste to a standard module.

Public Function Anniversary(ByVal pDate As Date, _
                            Optional pNextOne As Boolean = False) As Date
'*******************************************
'Purpose:   Return this years' or the next
'           anniversary of an input date
'Source:    http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=48683
'Coded by:  raskew
'*******************************************

Dim MyDate As Date

    MyDate = pDate
    
    'this line provides the anniversary date occuring this year
    MyDate = DateSerial(Year(Date), Month(MyDate), Day(MyDate))
    
    'this line allows the user to specify the next scheduled anniversary
    'if this years' anniversary has already past
    'note that the second and third options come into play only if pNext = True
    MyDate = Switch(pNextOne = False, MyDate, MyDate < Date, DateAdd("yyyy", 1, MyDate), True, MyDate)
    
    Anniversary = MyDate

End Function

HTH - Bob