quick simple question

[ifoundgoldbug]

Greetings once again

I am trying to automate our Preventative maintence program. My boss
requested that the PM's for the week be automatically e-mailed to him
every monday. so I need to store a date for reference the first record
of field Email sent. to reference against Date().

I was thinking of doing a check like

<pseudo code>

if (Date() - LastEmail) >6 OR date() = VbMonday then

send e-mail
LastEmail = Date()


End if

</pseudo code>


as always your help is appreciated

 

[Stefan Hoffmann]

hi,

if (Date() - LastEmail) >6 OR

This is a faster solution than using DateDiff(), otherwise DateDiff() is
more self-explanatory.

date() = VbMonday then

Weekday(Date(), vbMonday) = vbMonday

send e-mail If SendEMail Then
LastEmail = Date()

End If

End if

Also consider using a variable for the date instead of the multiple
Date() calls.


mfG
--> stefan <--

 

[ifoundgoldbug]

thanks for the help. that looks a lot cleaner.

but how do can I access just the first record of field LastEmail using
code?

thanks again for your help

Gold Bug

 

[Stefan Hoffmann]

hi,

but how do can I access just the first record of field LastEmail using
code?

Guess you have table PM(ID, ..., LastSentate/Time).

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "SELECT TOP 1 * FROM PM ORDER BY ID ASC", _
CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
Debug.? rs![LastSent]
rs.Close

Set rs = Nothing


or

rs.Open "SELECT * FROM PM " & _
"WHERE (Date()-LastSent)>6 " & _
"AND DatePart('w', ExecutionDate) = DatePart('w', Date())", _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Do While Not rs.Eof
'....
rs![LastSent] = Date()
rs.Update
rs.MoveNext
Loop
rs.Close


mfG
--> stefan <--