Creating a "Days since" field

  • Thread starter Thread starter Thomas N
  • Start date Start date
T

Thomas N

Hello,

I'm not quite sure how to start with this one, so I
thought I would just ask here..

I get reports from other companies, some at a regular
interval, while others on a more random interval.

What I want to do is create a pretty simple form and
table. The table currently consists of "Name" and "Last
Received". The Form then shows these two fields. I want
to add a third field that takes the date of the current
day, and tells me how many days it's been since I last
received a report from this company. Is that a hard thing
to create?

I would love also, if when reaching a certain number of
days, say 60 or so, maybe a pop up window, or another
field would appear in the form, showing that this one
is 'overdue'.

Anyway... any type of info on how to create this would be
appreciated. I'm pretty new at this, so don't expect that
I know too much!

Thanks,
Tom
 
Hello,

I'm not quite sure how to start with this one, so I
thought I would just ask here..

I get reports from other companies, some at a regular
interval, while others on a more random interval.

What I want to do is create a pretty simple form and
table. The table currently consists of "Name" and "Last
Received". The Form then shows these two fields. I want
to add a third field that takes the date of the current
day, and tells me how many days it's been since I last
received a report from this company. Is that a hard thing
to create?

I would love also, if when reaching a certain number of
days, say 60 or so, maybe a pop up window, or another
field would appear in the form, showing that this one
is 'overdue'.

Anyway... any type of info on how to create this would be
appreciated. I'm pretty new at this, so don't expect that
I know too much!

Thanks,
Tom
Click to expand...

First an important word from Access.....
1) "Name" is a reserved Access word and should not be used as a field
name. See the appropriate Microsoft KnowledgeBase article for your
version of Access for a list of words not to be used.

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

2) To determine the difference in Days between a date field and the
current date:
=DateDiff("d",[LastReceived],Date())

in the control source of an unbopund control on your form.

3) If you are using Access 2000 or later, a simple method to draw
attention to a past due date is to use Conditional Formatting.

Or (in any version) ... if you are in single form view, code the
form's Current event:
If DateDiff("d",[LastReceived],DateI()) >=60 Then
[LastReceived].BackColor = vbRed
[LastReceived].ForeColor = vbWhite
Else
[LastReceived].BackColor = vbWhite
[LastReceived].ForeColor = vbBlack
End If

change the colors as wanted.

4) Otherwise you could run a query once a day to display all past due
dates:
Select YourTable.* from YourTable Where
DateDiff("d",[LastReceived],Date())>=60;
 
Thank you very much, Fred, I will give this a try.

Tom
-----Original Message-----
Hello,

I'm not quite sure how to start with this one, so I
thought I would just ask here..

I get reports from other companies, some at a regular
interval, while others on a more random interval.

What I want to do is create a pretty simple form and
table. The table currently consists of "Name" and "Last
Received". The Form then shows these two fields. I want
to add a third field that takes the date of the current
day, and tells me how many days it's been since I last
received a report from this company. Is that a hard thing
to create?

I would love also, if when reaching a certain number of
days, say 60 or so, maybe a pop up window, or another
field would appear in the form, showing that this one
is 'overdue'.

Anyway... any type of info on how to create this would be
appreciated. I'm pretty new at this, so don't expect that
I know too much!

Thanks,
Tom
Click to expand...

First an important word from Access.....
1) "Name" is a reserved Access word and should not be used as a field
name. See the appropriate Microsoft KnowledgeBase article for your
version of Access for a list of words not to be used.

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

2) To determine the difference in Days between a date field and the
current date:
=DateDiff("d",[LastReceived],Date())

in the control source of an unbopund control on your form.

3) If you are using Access 2000 or later, a simple method to draw
attention to a past due date is to use Conditional Formatting.

Or (in any version) ... if you are in single form view, code the
form's Current event:
If DateDiff("d",[LastReceived],DateI()) >=60 Then
[LastReceived].BackColor = vbRed
[LastReceived].ForeColor = vbWhite
Else
[LastReceived].BackColor = vbWhite
[LastReceived].ForeColor = vbBlack
End If

change the colors as wanted.

4) Otherwise you could run a query once a day to display all past due
dates:
Select YourTable.* from YourTable Where
DateDiff("d",[LastReceived],Date())>=60;
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Tom,

Would you be interested in automating the whole process? The reports could be
imported into a database with a click of a button or two. From the database you
could then output the data in whatever reports you needed; again with a click of
a button or two. Contact me me interested.
 
In a Query:
Select [Name],LastReceived,Int(Date() - LastReceived) as
DaysPassed From Table


Chris

-----Original Message-----
Thank you very much, Fred, I will give this a try.

Tom
-----Original Message-----
Hello,

I'm not quite sure how to start with this one, so I
thought I would just ask here..

I get reports from other companies, some at a regular
interval, while others on a more random interval.

What I want to do is create a pretty simple form and
table. The table currently consists of "Name" and "Last
Received". The Form then shows these two fields. I want
to add a third field that takes the date of the current
day, and tells me how many days it's been since I last
received a report from this company. Is that a hard thing
to create?

I would love also, if when reaching a certain number of
days, say 60 or so, maybe a pop up window, or another
field would appear in the form, showing that this one
is 'overdue'.

Anyway... any type of info on how to create this would be
appreciated. I'm pretty new at this, so don't expect that
I know too much!

Thanks,
Tom
Click to expand...

First an important word from Access.....
1) "Name" is a reserved Access word and should not be used as a field
name. See the appropriate Microsoft KnowledgeBase article for your
version of Access for a list of words not to be used.

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

2) To determine the difference in Days between a date field and the
current date:
=DateDiff("d",[LastReceived],Date())

in the control source of an unbopund control on your form.

3) If you are using Access 2000 or later, a simple method to draw
attention to a past due date is to use Conditional Formatting.

Or (in any version) ... if you are in single form view, code the
form's Current event:
If DateDiff("d",[LastReceived],DateI()) >=60 Then
[LastReceived].BackColor = vbRed
[LastReceived].ForeColor = vbWhite
Else
[LastReceived].BackColor = vbWhite
[LastReceived].ForeColor = vbBlack
End If

change the colors as wanted.

4) Otherwise you could run a query once a day to display all past due
dates:
Select YourTable.* from YourTable Where
DateDiff("d",[LastReceived],Date())>=60;
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
.
Click to expand...
 
Back
Top