ADDING A FORMULA TO A CELL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

geeves1293 - 24 Jul 2006 16:21 GMT

I am trying to add a formula to a cell which looks at a date in the next
field and finds out if that date is < Now(), if so then send email.

If this is at all possible please help!!

Many Thanks

Reply to this Message

fredg - 24 Jul 2006 17:27 GMT

> I am trying to add a formula to a cell which looks at a date in the next
> field and finds out if that date is < Now(), if so then send email.
>
> If this is at all possible please help!!
>
> Many Thanks

You can't.
You're using Access, not Excel.
Access has fields, not cells, and you cannot write a formula into an
access table field.
You'll have to use an unbound control on a form, or a column in a
query.
For example, in a query:
SendMail:IIf([DateField]<Date(),"Yes","No")

Then you'll need to write code to do whatever you want to do, using as
criteria in the query:
Where QueryName.SendMail = "Yes"

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

geeves1293 - 24 Jul 2006 17:51 GMT

> > I am trying to add a formula to a cell which looks at a date in the next
> > field and finds out if that date is < Now(), if so then send email.
[quoted text clipped - 15 lines]
> criteria in the query:
> Where QueryName.SendMail = "Yes"

Thank you for responding.
I'm still having difficulties.
I have a table which has a number of columns including one which has dates
in it. If I open a query and put your code
SendMail:IIf([DateField]<Date(),"Yes","No") in the criteria of the dates
column, will this work. And sorry but I don't understand about your next
paragraph.

Thanks again for your help. Very much appreicated.

Reply to this Message

fredg - 24 Jul 2006 18:50 GMT

>>> I am trying to add a formula to a cell which looks at a date in the next
>>> field and finds out if that date is < Now(), if so then send email.
[quoted text clipped - 25 lines]
>
> Thanks again for your help. Very much appreicated.

Well, you haven't really been specific about what it is you are
attempting to do, so it's difficult to give you a specific reply.
I think you are attempting to send an email to persons who's datefield
is earlier than today.
You will need code to send an email automatically.
You will also need to filter out the records that are earlier than
today.
So.... to filter records, create a new query.
Include all the fields that are necessary.
Then add the new column SendMail (as shown in my previous reply).
Make sure you change [DateField] to the actual name of your date
field.
That column will read either "Yes" or "No" for each record.
Then, on the criteria line on this SendMail field, write:
"Yes" (include the quotes)

When you run the query only records that are Yes (earlier than today)
will be returned.

Then to actually send emails to these people you will need code.
See: EmailSenate and EmailSenate2K at
http://www.datastrat.com/DataStrat2.html
for a sample database that will send multiple e-mails.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

geeves1293 - 25 Jul 2006 15:33 GMT

THAT HAS SORTED IT OUT. I CANT SAY THANK YOU ENOUGH.

CHEERS

GRAHAM

> >>> I am trying to add a formula to a cell which looks at a date in the next
> >>> field and finds out if that date is < Now(), if so then send email.
[quoted text clipped - 49 lines]
> http://www.datastrat.com/DataStrat2.html
> for a sample database that will send multiple e-mails.

Reply to this Message

Jerry Whittle - 24 Jul 2006 17:30 GMT

It's possible - in Excel. It is not possible in the fields of an Access
table which does NOT have cells. That table might look like a spreadsheet;
however, it is not. It is used just to store data. Formula needs to go into
queries, forms and reports.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Reply to this Message