Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Does any1 know what is the best way2progam DB to send BirthDay wish to employees ?

    Dear All,
    I am trying to figure out how to program the database to automatically send Birth Day wish (Standard Email) to all those employees whose date of birth false in any particular date e.g. if your Date of Birth is 06/02/07 then the database should automatically prompt me(the user) whether he/she allows the DB to send the birthday wish email to you(or anyone else having the same date of birth) assume 20 people have the same date of birth e.g. 11/05/07 then the database should send the same email to all those 20 people and so on?

    Any help would be MUCH appreciated.

    regards
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I beleive you can create a macro that sends e-mail using the SendObject function. Have a play around with that just to send a simple test e-mail.

    As for selecting the employees - you could easily do this with SQL:

    SELECT employee_name, employe_email
    FROM employees
    WHERE birth_date = getdate()

    The above is just psuedo code and will need some work, but hopefully enough to get you started?

    - GeorgeV
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    DoCmd.SendObject in VBScript will allow you to put variables in for the email address and subject etc
    Which should also help you with an iterative loop to run it n amount of times.
    Last edited by gvee; 02-07-07 at 13:38.
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    SELECT employee_name, employe_email
    FROM employees
    WHERE birth_date = getdate()

    The above is just psuedo code and will need some work,
    george, slow down and think about it for a minute

    getdate() returns a datetime with accuracy to the second

    your query returns those rows from the database where the person was born at the exact second that you run the query?

    i think you're going to find 0 rows no matter how many times you run it...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aye, it was just a start point - been "told off" in the past for posting far too much of a solution :P

    Won't learn if the answer is given to you on a plate, eh?

    thanks for deleting that spam thread a min ago btw
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i should think that posting a bad solution is even worse than just handing the poor person the correct solution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by georgev
    The above is just psuedo code and will need some work, but hopefully enough to get you started?
    But ok...

    WHERE birth_date = GetDate() will not work as r937 mentioned above.
    Try playing about with the datepart function or Day, Month, Year ++

    cheers r937
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Realised another fault in the above...
    If we're looking at the birthdate...
    None of your employees birthdates will be GetDate() (for today that's 07/02/2007... - none of your employees were born today... I hope!)
    You want to compare with LIKE '07/02%'

    Correct me if I'm wrong please r9
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Realised another fault in the above...
    If we're looking at the birthdate...
    None of your employees birthdates will be GetDate() (for today that's 07/02/2007... - none of your employees were born today... I hope!)
    so you finally understand!


    Quote Originally Posted by georgev
    You want to compare with LIKE '07/02%'
    no!

    a datetime column is not the same as a string column, you cannot use LIKE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    just compare the dates in a "dd/MMM" format remember lower case m is minutes
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hmmm, I'm going round in the wrong circle here...

    I realise the LIKE is wrong, thanks for pointing that out

    m.timoney, how do you compare them in certain formats?

    Hooray for learning!
    George
    Home | Blog

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

    Ot

    Quote Originally Posted by georgev
    Aye, it was just a start point - been "told off" in the past for posting far too much of a solution :P

    Won't learn if the answer is given to you on a plate, eh?

    thanks for deleting that spam thread a min ago btw

    I didnt intend to "tell anyone off".... its merely my preference to post answers that encourage the questioner to think about the problem they've got, first and see if they can develop a home grown solution. if they get a quick easy full solution every time then they will keep coming back demanding solutions rather than seeking guidance or assistance.

    far too many questioners either dont use, wont use or possibly dont even know there is a help system bundled with access, or think of looking at MSDN, which in my view are the tools of first resort, then after that try forums like dbforums. Or they know about them but cannot be bothered to look. Too often on client code I see biots of code plopped into a form, report, or module where it obvious its come form another source, but there is noo knowledge or effort on behalf of the coder to understand what is going on.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I didn't mean that!
    I just meant that (I gess a situation similar to this where I got hooked on a prolem and finally solved it) I stuck my full solution proudly for all to see...

    Which was the wrong way to do it

    I totally agree with what you're saying healdem, so don't apologise!
    George
    Home | Blog

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by m.timoney
    just compare the dates in a "dd/MMM" format remember lower case m is minutes
    Right, so back to the problem...
    I can't work out a way of doing the above suggestion
    George
    Home | Blog

  15. #15
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Look at the FORMAT function in BOL.

    If FORMAT(Now(),"dd/MMM")=FORMAT(datefield,"dd/MMM")...
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •