Parameter to String?

[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 = "(e-mail address removed)"
objEmail.To = "(e-mail address removed); (e-mail address removed)"
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

 

[Ron Hinds]

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 = "(e-mail address removed)"
objEmail.To = "(e-mail address removed); (e-mail address removed)"
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

Modiify it like so:

Sub Email(dtmReportDate As Date)

Set objEmail = CreateObject("CDO.Message")
objEmail.From = "(e-mail address removed)"
objEmail.To = "(e-mail address removed); (e-mail address removed)"
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.

 

[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

 

[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,

 

[Ron Hinds]

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

 

[Guest]

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

 

[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"

 

[Douglas J Steele]

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.)