Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Parameter to String?

Discussion in 'Information Technology' started by Cadstillo, Nov 4, 2005.

  1. Cadstillo

    Cadstillo
    Expand Collapse
    Guest

    I have created a report in MS Access 2003 that is run daily, it prompts the
    user for the date by using a date parameter. The user enters the previous
    days date and displays the records for that date. If there are no records for
    the report for the specified date a dialog box is displayed stating "There
    are no records to view". In addition to the dialog box an email is sent to
    several people informing them that there were no records for yesterday
    (Yesterday: dtmYesterday = (Now) -1).

    On Monday I run the report for Sunday, Saturday and Friday: because the
    report was ran on Monday I either have to change the system date so the
    correct date appears in the email generated.

    What I am wanting to do is pass the date parameter to the Sub so I can use
    the date of the report instead of Yesterday's date, because if I do not
    always run the report everyday.

    Any suggestions?

    The Code to send the email is:

    ===============================================
    Sub Email()
    Set objEmail = CreateObject("CDO.Message")
    dtmYesterday = (Now) - 1
    'MsgBox dtmYesterday
    objEmail.From = "reports@info.com"
    objEmail.To = "reportviewer@reports.com; manager@reports.com"
    objEmail.Subject = "Daily Reports"
    objEmail.Textbody = "You will not be able to view the report for " &
    dtmYesterday & vbCrLf & vbCrLf & ". Because there was no pertinent activity "
    & dtmYesterday
    objEmail.Send

    End Sub
    =================================================

    Thank you

    --
    Cadstillo
     
  2. Loading...


  3. Ron Hinds

    Ron Hinds
    Expand Collapse
    Guest

    "Cadstillo" <Cadstillo@discussions.microsoft.com> wrote in message
    news:1D85E56C-DE6A-45CA-8E98-99F1C5100766@microsoft.com...
    > I have created a report in MS Access 2003 that is run daily, it prompts

    the
    > user for the date by using a date parameter. The user enters the previous
    > days date and displays the records for that date. If there are no records

    for
    > the report for the specified date a dialog box is displayed stating "There
    > are no records to view". In addition to the dialog box an email is sent to
    > several people informing them that there were no records for yesterday
    > (Yesterday: dtmYesterday = (Now) -1).
    >
    > On Monday I run the report for Sunday, Saturday and Friday: because the
    > report was ran on Monday I either have to change the system date so the
    > correct date appears in the email generated.
    >
    > What I am wanting to do is pass the date parameter to the Sub so I can use
    > the date of the report instead of Yesterday's date, because if I do not
    > always run the report everyday.
    >
    > Any suggestions?
    >
    > The Code to send the email is:
    >
    > ===============================================
    > Sub Email()
    > Set objEmail = CreateObject("CDO.Message")
    > dtmYesterday = (Now) - 1
    > 'MsgBox dtmYesterday
    > objEmail.From = "reports@info.com"
    > objEmail.To = "reportviewer@reports.com; manager@reports.com"
    > objEmail.Subject = "Daily Reports"
    > objEmail.Textbody = "You will not be able to view the report for " &
    > dtmYesterday & vbCrLf & vbCrLf & ". Because there was no pertinent

    activity "
    > & dtmYesterday
    > objEmail.Send
    >
    > End Sub
    > =================================================
    >
    > Thank you
    >
    > --
    > Cadstillo


    Modiify it like so:

    Sub Email(dtmReportDate As Date)

    Set objEmail = CreateObject("CDO.Message")
    objEmail.From = "reports@info.com"
    objEmail.To = "reportviewer@reports.com; manager@reports.com"
    objEmail.Subject = "Daily Reports"
    objEmail.Textbody = "You will not be able to view the report for " &
    dtmReportDate & vbCrLf & vbCrLf & ". Because there was no pertinent
    activity "
    & dtmReportDate
    objEmail.Send

    End Sub

    In the calling routine simply pass whatever date they typed in as a
    parameter to Email.
     
  4. Cadstillo

    Cadstillo
    Expand Collapse
    Guest

    Did I mention that this email was sent only if there were no records? Now I
    am getting a Compile Error: Argument not optional. I assume it is because
    there are now records.

    Thanks
    --
    Cadstillo



    "Ron Hinds" wrote:

    > "Cadstillo" <Cadstillo@discussions.microsoft.com> wrote in message
    > news:1D85E56C-DE6A-45CA-8E98-99F1C5100766@microsoft.com...
    > > I have created a report in MS Access 2003 that is run daily, it prompts

    > the
    > > user for the date by using a date parameter. The user enters the previous
    > > days date and displays the records for that date. If there are no records

    > for
    > > the report for the specified date a dialog box is displayed stating "There
    > > are no records to view". In addition to the dialog box an email is sent to
    > > several people informing them that there were no records for yesterday
    > > (Yesterday: dtmYesterday = (Now) -1).
    > >
    > > On Monday I run the report for Sunday, Saturday and Friday: because the
    > > report was ran on Monday I either have to change the system date so the
    > > correct date appears in the email generated.
    > >
    > > What I am wanting to do is pass the date parameter to the Sub so I can use
    > > the date of the report instead of Yesterday's date, because if I do not
    > > always run the report everyday.
    > >
    > > Any suggestions?
    > >
    > > The Code to send the email is:
    > >
    > > ===============================================
    > > Sub Email()
    > > Set objEmail = CreateObject("CDO.Message")
    > > dtmYesterday = (Now) - 1
    > > 'MsgBox dtmYesterday
    > > objEmail.From = "reports@info.com"
    > > objEmail.To = "reportviewer@reports.com; manager@reports.com"
    > > objEmail.Subject = "Daily Reports"
    > > objEmail.Textbody = "You will not be able to view the report for " &
    > > dtmYesterday & vbCrLf & vbCrLf & ". Because there was no pertinent

    > activity "
    > > & dtmYesterday
    > > objEmail.Send
    > >
    > > End Sub
    > > =================================================
    > >
    > > Thank you
    > >
    > > --
    > > Cadstillo

    >
    > Modiify it like so:
    >
    > Sub Email(dtmReportDate As Date)
    >
    > Set objEmail = CreateObject("CDO.Message")
    > objEmail.From = "reports@info.com"
    > objEmail.To = "reportviewer@reports.com; manager@reports.com"
    > objEmail.Subject = "Daily Reports"
    > objEmail.Textbody = "You will not be able to view the report for " &
    > dtmReportDate & vbCrLf & vbCrLf & ". Because there was no pertinent
    > activity "
    > & dtmReportDate
    > objEmail.Send
    >
    > End Sub
    >
    > In the calling routine simply pass whatever date they typed in as a
    > parameter to Email.
    >
    >
    >
     
  5. Cadstillo

    Cadstillo
    Expand Collapse
    Guest

    Private Sub Report_NoData(Cancel As Integer)
    'strDate = Date
    MsgBox "There are no records to report.", vbExclamation, "No Records"
    Cancel = True
    Email
    End Sub

    I use the Sub above to notify the end user that there were no records and
    then the email sub to report to management that there were no records.

    Thanks,
    --
    Cadstillo



    "Cadstillo" wrote:

    > I have created a report in MS Access 2003 that is run daily, it prompts the
    > user for the date by using a date parameter. The user enters the previous
    > days date and displays the records for that date. If there are no records for
    > the report for the specified date a dialog box is displayed stating "There
    > are no records to view". In addition to the dialog box an email is sent to
    > several people informing them that there were no records for yesterday
    > (Yesterday: dtmYesterday = (Now) -1).
    >
    > On Monday I run the report for Sunday, Saturday and Friday: because the
    > report was ran on Monday I either have to change the system date so the
    > correct date appears in the email generated.
    >
    > What I am wanting to do is pass the date parameter to the Sub so I can use
    > the date of the report instead of Yesterday's date, because if I do not
    > always run the report everyday.
    >
    > Any suggestions?
    >
    > The Code to send the email is:
    >
    > ===============================================
    > Sub Email()
    > Set objEmail = CreateObject("CDO.Message")
    > dtmYesterday = (Now) - 1
    > 'MsgBox dtmYesterday
    > objEmail.From = "reports@info.com"
    > objEmail.To = "reportviewer@reports.com; manager@reports.com"
    > objEmail.Subject = "Daily Reports"
    > objEmail.Textbody = "You will not be able to view the report for " &
    > dtmYesterday & vbCrLf & vbCrLf & ". Because there was no pertinent activity "
    > & dtmYesterday
    > objEmail.Send
    >
    > End Sub
    > =================================================
    >
    > Thank you
    >
    > --
    > Cadstillo
    >
     
  6. Ron Hinds

    Ron Hinds
    Expand Collapse
    Guest

    No. the "Argument not Optional" compile error is because you have not
    supplied the date in your calling routine. Like so:

    Private Sub Report_NoData(Cancel As Integer)
    strDate = Date
    MsgBox "There are no records to report.", vbExclamation, "No Records"
    Cancel = True
    Call Email(strDate)
    End Sub

    "Cadstillo" <Cadstillo@discussions.microsoft.com> wrote in message
    news:3CC074B5-B02D-4842-869B-2849B8C8EDCB@microsoft.com...
    > Did I mention that this email was sent only if there were no records? Now

    I
    > am getting a Compile Error: Argument not optional. I assume it is because
    > there are now records.
    >
    > Thanks
    > --
    > Cadstillo
    >
    >
    >
    > "Ron Hinds" wrote:
    >
    > > "Cadstillo" <Cadstillo@discussions.microsoft.com> wrote in message
    > > news:1D85E56C-DE6A-45CA-8E98-99F1C5100766@microsoft.com...
    > > > I have created a report in MS Access 2003 that is run daily, it

    prompts
    > > the
    > > > user for the date by using a date parameter. The user enters the

    previous
    > > > days date and displays the records for that date. If there are no

    records
    > > for
    > > > the report for the specified date a dialog box is displayed stating

    "There
    > > > are no records to view". In addition to the dialog box an email is

    sent to
    > > > several people informing them that there were no records for yesterday
    > > > (Yesterday: dtmYesterday = (Now) -1).
    > > >
    > > > On Monday I run the report for Sunday, Saturday and Friday: because

    the
    > > > report was ran on Monday I either have to change the system date so

    the
    > > > correct date appears in the email generated.
    > > >
    > > > What I am wanting to do is pass the date parameter to the Sub so I can

    use
    > > > the date of the report instead of Yesterday's date, because if I do

    not
    > > > always run the report everyday.
    > > >
    > > > Any suggestions?
    > > >
    > > > The Code to send the email is:
    > > >
    > > > ===============================================
    > > > Sub Email()
    > > > Set objEmail = CreateObject("CDO.Message")
    > > > dtmYesterday = (Now) - 1
    > > > 'MsgBox dtmYesterday
    > > > objEmail.From = "reports@info.com"
    > > > objEmail.To = "reportviewer@reports.com; manager@reports.com"
    > > > objEmail.Subject = "Daily Reports"
    > > > objEmail.Textbody = "You will not be able to view the report for " &
    > > > dtmYesterday & vbCrLf & vbCrLf & ". Because there was no pertinent

    > > activity "
    > > > & dtmYesterday
    > > > objEmail.Send
    > > >
    > > > End Sub
    > > > =================================================
    > > >
    > > > Thank you
    > > >
    > > > --
    > > > Cadstillo

    > >
    > > Modiify it like so:
    > >
    > > Sub Email(dtmReportDate As Date)
    > >
    > > Set objEmail = CreateObject("CDO.Message")
    > > objEmail.From = "reports@info.com"
    > > objEmail.To = "reportviewer@reports.com; manager@reports.com"
    > > objEmail.Subject = "Daily Reports"
    > > objEmail.Textbody = "You will not be able to view the report for " &
    > > dtmReportDate & vbCrLf & vbCrLf & ". Because there was no pertinent
    > > activity "
    > > & dtmReportDate
    > > objEmail.Send
    > >
    > > End Sub
    > >
    > > In the calling routine simply pass whatever date they typed in as a
    > > parameter to Email.
    > >
    > >
    > >
     
  7. Cadstillo

    Cadstillo
    Expand Collapse
    Guest

    Cool, I will try it and post the success here.

    --
    Cadstillo



    "Cadstillo" wrote:

    > Private Sub Report_NoData(Cancel As Integer)
    > 'strDate = Date
    > MsgBox "There are no records to report.", vbExclamation, "No Records"
    > Cancel = True
    > Email
    > End Sub
    >
    > I use the Sub above to notify the end user that there were no records and
    > then the email sub to report to management that there were no records.
    >
    > Thanks,
    > --
    > Cadstillo
    >
    >
    >
    > "Cadstillo" wrote:
    >
    > > I have created a report in MS Access 2003 that is run daily, it prompts the
    > > user for the date by using a date parameter. The user enters the previous
    > > days date and displays the records for that date. If there are no records for
    > > the report for the specified date a dialog box is displayed stating "There
    > > are no records to view". In addition to the dialog box an email is sent to
    > > several people informing them that there were no records for yesterday
    > > (Yesterday: dtmYesterday = (Now) -1).
    > >
    > > On Monday I run the report for Sunday, Saturday and Friday: because the
    > > report was ran on Monday I either have to change the system date so the
    > > correct date appears in the email generated.
    > >
    > > What I am wanting to do is pass the date parameter to the Sub so I can use
    > > the date of the report instead of Yesterday's date, because if I do not
    > > always run the report everyday.
    > >
    > > Any suggestions?
    > >
    > > The Code to send the email is:
    > >
    > > ===============================================
    > > Sub Email()
    > > Set objEmail = CreateObject("CDO.Message")
    > > dtmYesterday = (Now) - 1
    > > 'MsgBox dtmYesterday
    > > objEmail.From = "reports@info.com"
    > > objEmail.To = "reportviewer@reports.com; manager@reports.com"
    > > objEmail.Subject = "Daily Reports"
    > > objEmail.Textbody = "You will not be able to view the report for " &
    > > dtmYesterday & vbCrLf & vbCrLf & ". Because there was no pertinent activity "
    > > & dtmYesterday
    > > objEmail.Send
    > >
    > > End Sub
    > > =================================================
    > >
    > > Thank you
    > >
    > > --
    > > Cadstillo
    > >
     
  8. Cadstillo

    Cadstillo
    Expand Collapse
    Guest

    Hmmm, I made the changes you said: (i.e. "Call Email(strDate)")

    now I am getting a

    "Compile error: By Ref argument type mismatch"

    --
    Cadstillo

    "Ron Hinds" wrote:

    > No. the "Argument not Optional" compile error is because you have not
    > supplied the date in your calling routine. Like so:
    >
    > Private Sub Report_NoData(Cancel As Integer)
    > strDate = Date
    > MsgBox "There are no records to report.", vbExclamation, "No Records"
    > Cancel = True
    > Call Email(strDate)
    > End Sub
    >
    > "Cadstillo" <Cadstillo@discussions.microsoft.com> wrote in message
    > news:3CC074B5-B02D-4842-869B-2849B8C8EDCB@microsoft.com...
    > > Did I mention that this email was sent only if there were no records? Now

    > I
    > > am getting a Compile Error: Argument not optional. I assume it is because
    > > there are now records.
    > >
    > > Thanks
    > > --
    > > Cadstillo
    > >
    > >
    > >
    > > "Ron Hinds" wrote:
    > >
    > > > "Cadstillo" <Cadstillo@discussions.microsoft.com> wrote in message
    > > > news:1D85E56C-DE6A-45CA-8E98-99F1C5100766@microsoft.com...
    > > > > I have created a report in MS Access 2003 that is run daily, it

    > prompts
    > > > the
    > > > > user for the date by using a date parameter. The user enters the

    > previous
    > > > > days date and displays the records for that date. If there are no

    > records
    > > > for
    > > > > the report for the specified date a dialog box is displayed stating

    > "There
    > > > > are no records to view". In addition to the dialog box an email is

    > sent to
    > > > > several people informing them that there were no records for yesterday
    > > > > (Yesterday: dtmYesterday = (Now) -1).
    > > > >
    > > > > On Monday I run the report for Sunday, Saturday and Friday: because

    > the
    > > > > report was ran on Monday I either have to change the system date so

    > the
    > > > > correct date appears in the email generated.
    > > > >
    > > > > What I am wanting to do is pass the date parameter to the Sub so I can

    > use
    > > > > the date of the report instead of Yesterday's date, because if I do

    > not
    > > > > always run the report everyday.
    > > > >
    > > > > Any suggestions?
    > > > >
    > > > > The Code to send the email is:
    > > > >
    > > > > ===============================================
    > > > > Sub Email()
    > > > > Set objEmail = CreateObject("CDO.Message")
    > > > > dtmYesterday = (Now) - 1
    > > > > 'MsgBox dtmYesterday
    > > > > objEmail.From = "reports@info.com"
    > > > > objEmail.To = "reportviewer@reports.com; manager@reports.com"
    > > > > objEmail.Subject = "Daily Reports"
    > > > > objEmail.Textbody = "You will not be able to view the report for " &
    > > > > dtmYesterday & vbCrLf & vbCrLf & ". Because there was no pertinent
    > > > activity "
    > > > > & dtmYesterday
    > > > > objEmail.Send
    > > > >
    > > > > End Sub
    > > > > =================================================
    > > > >
    > > > > Thank you
    > > > >
    > > > > --
    > > > > Cadstillo
    > > >
    > > > Modiify it like so:
    > > >
    > > > Sub Email(dtmReportDate As Date)
    > > >
    > > > Set objEmail = CreateObject("CDO.Message")
    > > > objEmail.From = "reports@info.com"
    > > > objEmail.To = "reportviewer@reports.com; manager@reports.com"
    > > > objEmail.Subject = "Daily Reports"
    > > > objEmail.Textbody = "You will not be able to view the report for " &
    > > > dtmReportDate & vbCrLf & vbCrLf & ". Because there was no pertinent
    > > > activity "
    > > > & dtmReportDate
    > > > objEmail.Send
    > > >
    > > > End Sub
    > > >
    > > > In the calling routine simply pass whatever date they typed in as a
    > > > parameter to Email.
    > > >
    > > >
    > > >

    >
    >
    >
     
  9. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    The routine is expecting a Date, and unless you've declared strDate as a
    date somewhere, it's likely a variant.

    It serves no purpose to put today's date into a variable and then pass the
    variable. Change your code to:

    Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There are no records to report.", vbExclamation, "No Records"
    Cancel = True
    Call Email(Date())
    End Sub

    (BTW, if your code didn't complain that you were using strDate without
    declaring it, that implies that you haven't set Access up to force variable
    declaration on the Module tab under Tools | Options. That's almost always a
    bad idea: you can waste hours tracking down problems that are caused by
    typos in your code.)


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Cadstillo" <Cadstillo@discussions.microsoft.com> wrote in message
    news:133ED94F-5AA3-46E1-B0D7-6E3629E262BB@microsoft.com...
    > Hmmm, I made the changes you said: (i.e. "Call Email(strDate)")
    >
    > now I am getting a
    >
    > "Compile error: By Ref argument type mismatch"
    >
    > --
    > Cadstillo
    >
    > "Ron Hinds" wrote:
    >
    > > No. the "Argument not Optional" compile error is because you have not
    > > supplied the date in your calling routine. Like so:
    > >
    > > Private Sub Report_NoData(Cancel As Integer)
    > > strDate = Date
    > > MsgBox "There are no records to report.", vbExclamation, "No Records"
    > > Cancel = True
    > > Call Email(strDate)
    > > End Sub
    > >
    > > "Cadstillo" <Cadstillo@discussions.microsoft.com> wrote in message
    > > news:3CC074B5-B02D-4842-869B-2849B8C8EDCB@microsoft.com...
    > > > Did I mention that this email was sent only if there were no records?

    Now
    > > I
    > > > am getting a Compile Error: Argument not optional. I assume it is

    because
    > > > there are now records.
    > > >
    > > > Thanks
    > > > --
    > > > Cadstillo
    > > >
    > > >
    > > >
    > > > "Ron Hinds" wrote:
    > > >
    > > > > "Cadstillo" <Cadstillo@discussions.microsoft.com> wrote in message
    > > > > news:1D85E56C-DE6A-45CA-8E98-99F1C5100766@microsoft.com...
    > > > > > I have created a report in MS Access 2003 that is run daily, it

    > > prompts
    > > > > the
    > > > > > user for the date by using a date parameter. The user enters the

    > > previous
    > > > > > days date and displays the records for that date. If there are no

    > > records
    > > > > for
    > > > > > the report for the specified date a dialog box is displayed

    stating
    > > "There
    > > > > > are no records to view". In addition to the dialog box an email is

    > > sent to
    > > > > > several people informing them that there were no records for

    yesterday
    > > > > > (Yesterday: dtmYesterday = (Now) -1).
    > > > > >
    > > > > > On Monday I run the report for Sunday, Saturday and Friday:

    because
    > > the
    > > > > > report was ran on Monday I either have to change the system date

    so
    > > the
    > > > > > correct date appears in the email generated.
    > > > > >
    > > > > > What I am wanting to do is pass the date parameter to the Sub so I

    can
    > > use
    > > > > > the date of the report instead of Yesterday's date, because if I

    do
    > > not
    > > > > > always run the report everyday.
    > > > > >
    > > > > > Any suggestions?
    > > > > >
    > > > > > The Code to send the email is:
    > > > > >
    > > > > > ===============================================
    > > > > > Sub Email()
    > > > > > Set objEmail = CreateObject("CDO.Message")
    > > > > > dtmYesterday = (Now) - 1
    > > > > > 'MsgBox dtmYesterday
    > > > > > objEmail.From = "reports@info.com"
    > > > > > objEmail.To = "reportviewer@reports.com; manager@reports.com"
    > > > > > objEmail.Subject = "Daily Reports"
    > > > > > objEmail.Textbody = "You will not be able to view the report for "

    &
    > > > > > dtmYesterday & vbCrLf & vbCrLf & ". Because there was no pertinent
    > > > > activity "
    > > > > > & dtmYesterday
    > > > > > objEmail.Send
    > > > > >
    > > > > > End Sub
    > > > > > =================================================
    > > > > >
    > > > > > Thank you
    > > > > >
    > > > > > --
    > > > > > Cadstillo
    > > > >
    > > > > Modiify it like so:
    > > > >
    > > > > Sub Email(dtmReportDate As Date)
    > > > >
    > > > > Set objEmail = CreateObject("CDO.Message")
    > > > > objEmail.From = "reports@info.com"
    > > > > objEmail.To = "reportviewer@reports.com; manager@reports.com"
    > > > > objEmail.Subject = "Daily Reports"
    > > > > objEmail.Textbody = "You will not be able to view the report for " &
    > > > > dtmReportDate & vbCrLf & vbCrLf & ". Because there was no pertinent
    > > > > activity "
    > > > > & dtmReportDate
    > > > > objEmail.Send
    > > > >
    > > > > End Sub
    > > > >
    > > > > In the calling routine simply pass whatever date they typed in as a
    > > > > parameter to Email.
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     

Share This Page