|
 |
access thread: Date Reminder
Message #1 by "Becky" <RDingwall@m...> on Wed, 27 Feb 2002 11:29:46
|
|
Could anyone tell me urgently how to do a query that brings out all
records with today's date in them. It is a marketing database and my
colleague wants to put in the date of her next call and on that date have
a list of all the people she is to call.
What should the code for the query read. I had a 14 day reminder from
the date last contacted as and although this works it is getting a little
hectic so we just want records to show with today's date as the date next
to contact.
IIf(DateDiff("d",[tblCompanies]![Last_Contacted_Date],Date())
>=14,True,False)
Another problem is that I now have three contacts and different next dates
to contact on the same record as they relate to the same company so have
had to call the fields different names so we
have "Next_Contact_Date", "Next_Contact_Date1" and "Next_Contact_Date2"
etc is there anyway of having all these list in the one sheet.
I would appreciate anyone's help.
Kind regards.
Message #2 by Barry Martin Dancis <bdancis@c...> on Wed, 27 Feb 2002 10:03:12 -0500
|
|
Becky
Add a filter to your where clause such as
Where int(dateField) = Date()
Use the int() function to strip off the minutes part of a date/time
field
Enjoy!
Barry
----- Original Message -----
From: "Becky" <RDingwall@m...>
To: "Access" <access@p...>
Sent: Wednesday, February 27, 2002 6:29 AM
Subject: [access] Date Reminder
> Could anyone tell me urgently how to do a query that brings out all
> records with today's date in them. It is a marketing database and my
> colleague wants to put in the date of her next call and on that date have
> a list of all the people she is to call.
>
> What should the code for the query read. I had a 14 day reminder from
> the date last contacted as and although this works it is getting a little
> hectic so we just want records to show with today's date as the date next
> to contact.
>
> IIf(DateDiff("d",[tblCompanies]![Last_Contacted_Date],Date())
> >=14,True,False)
>
> Another problem is that I now have three contacts and different next dates
> to contact on the same record as they relate to the same company so have
> had to call the fields different names so we
> have "Next_Contact_Date", "Next_Contact_Date1" and "Next_Contact_Date2"
> etc is there anyway of having all these list in the one sheet.
>
> I would appreciate anyone's help.
>
> Kind regards.
>
>
Message #3 by Barry Martin Dancis <bdancis@c...> on Wed, 27 Feb 2002 11:32:59 -0500
|
|
Becky,
The following code should work:
select Next_Contact1 as Contact, [Next_Contact_Date1] as "Contact_Date"
from tblCompanies
where int(Next_Contact_Date1) = Date()
union
select Next_Contact2 as Contact, [Next_Contact_Date2] as "Contact_Date"
from tblCompanies
where int(Next_Contact_Date2) = Date()
union
select Next_Contact3 as Contact, [Next_Contact_Date3] as "Contact_Date"
from tblCompanies
where int(Next_Contact_Date3) = Date()
order by 1
Remember to use the int() function to eliminate times from the dates for the
comparisons
Enjoy!
Barry
----- Original Message -----
From: "Becky" <RDingwall@m...>
To: "Access" <access@p...>
Sent: Wednesday, February 27, 2002 6:29 AM
Subject: [access] Date Reminder
> Could anyone tell me urgently how to do a query that brings out all
> records with today's date in them. It is a marketing database and my
> colleague wants to put in the date of her next call and on that date have
> a list of all the people she is to call.
>
> What should the code for the query read. I had a 14 day reminder from
> the date last contacted as and although this works it is getting a little
> hectic so we just want records to show with today's date as the date next
> to contact.
>
> IIf(DateDiff("d",[tblCompanies]![Last_Contacted_Date],Date())
> >=14,True,False)
>
> Another problem is that I now have three contacts and different next dates
> to contact on the same record as they relate to the same company so have
> had to call the fields different names so we
> have "Next_Contact_Date", "Next_Contact_Date1" and "Next_Contact_Date2"
> etc is there anyway of having all these list in the one sheet.
>
> I would appreciate anyone's help.
>
> Kind regards.
>
>
|
|
|
View Cart
