Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50

    Unanswered: Remove Year from Date in a query

    I have a form that sends an E-Mail to people if There [Birthday] = [today]

    The problem is that I have the [Birthday] stored as Month,Day,Year. I have the code below to check if [Birthday] = [Today]. I don't want to change the query that fFBirthdays is based off of because that is a complicated query that Rudy built for me long ago.

    How can I change [Today] and [Birthday] to just Month and day and remove the year in the date.

    Hope this makes sense and Thanks


    If [Forms]![Switchboard]![fFBirthdays]![today] = [Forms]![Switchboard]![fFBirthdays]![birthday] Then
    If MsgBox(" " & [Forms]![Switchboard]![fFBirthdays]![FullName] & " has a birthday today. " & _
    "Would you like to send an E-mail to " & [Forms]![Switchboard]![fFBirthdays]![FullName] & "?", vbYesNo _
    , "Happy Birthday E-Mail") = vbYes Then
    Call SendRRMailEE
    Else
    MsgBox "E-mail has been cancelled", vbInformation, "E-Mail cancelled"
    End If
    End If
    Last edited by hooks; 08-06-04 at 13:41.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Use datepart() to pull the month and day.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would use the Format function to put each date in dd/mm format then do the comparison.

    If Format([Forms]![Switchboard]![fFBirthdays]![today],"dd/mm") = Format([Forms]![Switchboard]![fFBirthdays]![birthday],"dd/mm") Then

  4. #4
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Thanks a bunch.

  5. #5
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Teddy, I got it working using DCKunkles suggestion. Just curious how would i do it using datepart()

  6. #6
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    write a wrapper function that compares 2 dates with datepart function. here is an example:
    Code:
    'Takes 2 dates and compares their month and day, if they are equal returns true
    'Usage:
    ' IsBirthday( Birthday, DateToCompare)
    ' example:
    ' if IsBirthday(MyBirthday,date()) then ...
    Public Function IsBirthday(Birthday As Date, ADate As Date) As Boolean
    	IsBirthday = False
    	If (DatePart("m", Birthday) = DatePart("m", ADate)) And (DatePart("d", Birthday) = DatePart("d", ADate)) Then IsBirthday = True
    End Function
    Last edited by ghozy; 08-06-04 at 14:21.
    ghozy.

  7. #7
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Thanks All

  8. #8
    Join Date
    May 2006
    Posts
    25
    Hey Hooks,

    That's exactly what I want my database to do! I'm interested in the part where you Call SendRRMailEE. How do you get it to send the email? Want to share your secret?

  9. #9
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    here is how i am sending the email I think. It has been awhile since i looked at this program.

    Code:
    Public Function SendEMail() As Boolean
    
        Dim objOutlook As Object
        Dim objEmailMessage As Object
        
        Dim EMailSubject As String          'What will be in the subject line of E-Mail
        Dim EMailBody As String             'What will be in the Body line of E-Mail
        Dim EMailRecipient As Variant       'What is the E-Mail address
        Dim EMailRecipientName As Variant   'The name of the E-Mail receiver
        Dim Attachment01 As String          'Link to the first attachment
        Dim Attachment02 As String          'Link to the Second attachment
        Dim Attachment03 As String          'Link to the Third attachment
        
        'Specify error handler
        On Error GoTo Err_SendEMailError
        
        
        EMailSubject = "Happy Birthday"                                         'What will be in the subject line of E-Mail
        EMailBody = "Happy Birthday"                                            'What will be in the Body line of E-Mail
        EMailRecipient = [Forms]![Switchboard]![fFBirthdays]![EMail1]           'What is the E-Mail address
        EMailRecipientName = [Forms]![Switchboard]![fFBirthdays]![FullName]     'The name of the E-Mail receiver
        'Attachment01 = "C:\Customers.txt"                                       'Link to the first attachment
        'Attachment02 = "C:\Customers.txt"                                      'Link to the Second attachment
        'Attachment03 = "C:\Customers.txt"                                      'Link to the Third attachment
        
        If IsNull(EMailRecipient) Then
            If MsgBox("" & EMailRecipientName & " does not have an E-Mail address." & _
                vbCrLf & vbCrLf & "Would you like to add a E-Mail address for " & EMailRecipientName _
                , vbYesNo, "Could not send E-Mail") = vbYes Then
    
                DoCmd.OpenForm "fFContacts", acNormal, , "ContactID =" & ([Forms]![Switchboard]![fFBirthdays]![ContactID]), acFormEdit, acWindowNormal
                                      
                GoTo Exit_SendEMailDone
            Else
                MsgBox "E-Mail Cancelled", vbInformation, "E-Mail Cancelled"
            End If
        End If
    
     'Create outlook objects
        'note, you need to add the Outlook reference in Modules Menu option Tools/References
        Set objOutlook = CreateObject("Outlook.Application")
        Set objEmailMessage = objOutlook.CreateItem(olMailItem)
        
        'Set subject
        objEmailMessage.Subject = EMailSubject
        
        'message body
        objEmailMessage.Body = EMailBody
        
        'attachments
        'objEmailMessage.Attachments.Add Attachment01
        'objEmailMessage.Attachments.Add Attachment 02
        'objEmailMessage.Attachments.Add Attachment 03
        
        'Set the email object
        objEmailMessage.Recipients.Add EMailRecipient
        
        'Resolve email address
        objEmailMessage.Recipients.ResolveAll
        
        objEmailMessage.Send
        
        MsgBox "Happy Birthday E-Mail has been sent to " & EMailRecipientName, vbInformation, "Send Mail"
        
        'Return success
        SendEMail = True
        GoTo Exit_SendEMailDone
     
    Err_SendEMailError:
        
        'Return failure and display error message
        SendEMail = False
        Debug.Print Err.Number & Err.Description
        If Err.Number = 287 Then  'No was selected in outlook security prompt
            MsgBox "" & EMailRecipientName & " did not receive the " & EMailSubject & _
            vbCrLf & vbCrLf & "E-Mail because No was selected from the Outlook security prompt." _
            , vbInformation, "Could not send E-Mail"
            GoTo Exit_SendEMailDone
            
        End If
        
            If Err.Number = -2147467259 Then   'Invalid Email address.  hooks123@blah,com  notice comma instead of period ,com
                MsgBox "" & EMailRecipientName & " did not receive the " & EMailSubject & _
                vbCrLf & vbCrLf & "E-Mail because their E-Mail Address   " & EMailRecipient & _
                vbCrLf & vbCrLf & "has an invalid character.  Please confirm E-Mail Address", vbInformation, "Could not send E-Mail"
                GoTo Exit_SendEMailDone
            End If
            
                If Err.Number = -2147024894# Then     'Attachment path doesn't exist
                    MsgBox "" & EMailRecipientName & " did not receive the " & EMailSubject & _
                    vbCrLf & vbCrLf & "E-Mail because one of the attached file or files doesn't exist" _
                    , vbInformation, "E-Mail Cancelled"
                    GoTo Exit_SendEMailDone
                End If
        
            Call Error
            GoTo Exit_SendEMailDone
        
    Exit_SendEMailDone:
        'Clear down the objects created
        On Error Resume Next
        Set objEmailMessage = Nothing
        Set objOutlook = Nothing
    End Function

Posting Permissions

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