if then statements - new to in access 97

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Howdy all

I have (i hope) a general question about if statementsin access 97.

I have a table that contains the following field names in Table_1
NAME DATE SENT STATUS
J.Doe 01/09/05 Sent
J.Hpe 05/09/05 Received
A.Don 09/09/05 To Be Sent

What i need to do is send emails based on the condition that the SENT_STATUS
= "to be sent" and the date being 7 days before and the name

so i assume it goes like this,
IF SENT_STATUS = "To Be Sent" AND Date<date() AND NAME="A.DON" THEN
send email
ELSE
do nothing

being new to module use however how do I reference the table fields within a
function?

Cheers
 
Try this

Function FunctionName()
Dim MyDb as database, MyRec as Recordset

Set MyDb=codedb
' Open the table with all the names that are not sent yet
Set MyRec=Mydb.openRecordset("Select * From TableName Where [DATE] < Date()
And [SENT_STATUS] = 'To Be Sent'")
If not MyRec.Eof then
' Loop the records
While not MyRec.eof
SendMail ' using myrec![Name]
MyRec.Edit
MyRec!SENT_STATUS = "Sent" ' Will update the field to sent so
the next time it wont send it again
MyRec.Update
Wend
End If

I would advice you changing the names of the date and name fields, they are
resurved names in access, but if you want to keep them, when you refer to
them put them inside a [].
IF SENT_STATUS = "To Be Sent" AND Date<date() AND NAME="A.DON" THEN
send email
ELSE
do nothing
 
Thanks for the response Ofer,

works fine!!!

Ofer said:
Try this

Function FunctionName()
Dim MyDb as database, MyRec as Recordset

Set MyDb=codedb
' Open the table with all the names that are not sent yet
Set MyRec=Mydb.openRecordset("Select * From TableName Where [DATE] < Date()
And [SENT_STATUS] = 'To Be Sent'")
If not MyRec.Eof then
' Loop the records
While not MyRec.eof
SendMail ' using myrec![Name]
MyRec.Edit
MyRec!SENT_STATUS = "Sent" ' Will update the field to sent so
the next time it wont send it again
MyRec.Update
Wend
End If

I would advice you changing the names of the date and name fields, they are
resurved names in access, but if you want to keep them, when you refer to
them put them inside a [].
IF SENT_STATUS = "To Be Sent" AND Date<date() AND NAME="A.DON" THEN
send email
ELSE
do nothing

--
I hope that helped
Good luck


Songoku said:
Howdy all

I have (i hope) a general question about if statementsin access 97.

I have a table that contains the following field names in Table_1
NAME DATE SENT STATUS
J.Doe 01/09/05 Sent
J.Hpe 05/09/05 Received
A.Don 09/09/05 To Be Sent

What i need to do is send emails based on the condition that the SENT_STATUS
= "to be sent" and the date being 7 days before and the name

so i assume it goes like this,
IF SENT_STATUS = "To Be Sent" AND Date<date() AND NAME="A.DON" THEN
send email
ELSE
do nothing

being new to module use however how do I reference the table fields within a
function?

Cheers
Click to expand...
Click to expand...
 
Back
Top