I need help

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

Guest

I need to create an expression for the following:

I have an age report that has to tell me which computer is ready to be
replaced. the computer has to be older than 5 years. so how should I write
the expression. In my table I have a field of purchased. Thanks
 
I need to create an expression for the following:

I have an age report that has to tell me which computer is ready to be
replaced. the computer has to be older than 5 years. so how should I write
the expression. In my table I have a field of purchased. Thanks
Click to expand...

In a query?
Add a new column.

ReplaceDate:DateDiff("yyyy", [Purchased], Date()) -
IIF(Format([Purchased], "mmdd") > Format(Date(), "mmdd"), 1, 0)

Directly in a form or on a report?
Use an Unbound text control.
Set it's control source to:

= DateDiff("yyyy",[Purchased],Date())-IIf(Format([Purchased],"mmdd" ) >
Format(Date(), "mmdd"),1,0)


You do know, I hope, that this computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.
 
Fred,

thanks once again for the info., however I'm getting some negative numbers
that I'm not sure what they mean. for example: my desktop was purchased
2/3/2003, I should be ready for refresh 2/3/2008. I want this query to tell
me that I'm ready for refresh. The ouput should be in numbers not dates.

Thanks

fredg said:
I need to create an expression for the following:

I have an age report that has to tell me which computer is ready to be
replaced. the computer has to be older than 5 years. so how should I write
the expression. In my table I have a field of purchased. Thanks
Click to expand...

In a query?
Add a new column.

ReplaceDate:DateDiff("yyyy", [Purchased], Date()) -
IIF(Format([Purchased], "mmdd") > Format(Date(), "mmdd"), 1, 0)

Directly in a form or on a report?
Use an Unbound text control.
Set it's control source to:

= DateDiff("yyyy",[Purchased],Date())-IIf(Format([Purchased],"mmdd" ) >
Format(Date(), "mmdd"),1,0)


You do know, I hope, that this computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.
Click to expand...
 
Fred,

thanks once again for the info., however I'm getting some negative numbers
that I'm not sure what they mean. for example: my desktop was purchased
2/3/2003, I should be ready for refresh 2/3/2008. I want this query to tell
me that I'm ready for refresh. The ouput should be in numbers not dates.

Thanks

fredg said:
I need to create an expression for the following:

I have an age report that has to tell me which computer is ready to be
replaced. the computer has to be older than 5 years. so how should I write
the expression. In my table I have a field of purchased. Thanks
Click to expand...

In a query?
Add a new column.

ReplaceDate:DateDiff("yyyy", [Purchased], Date()) -
IIF(Format([Purchased], "mmdd") > Format(Date(), "mmdd"), 1, 0)

Directly in a form or on a report?
Use an Unbound text control.
Set it's control source to:

= DateDiff("yyyy",[Purchased],Date())-IIf(Format([Purchased],"mmdd" ) >
Format(Date(), "mmdd"),1,0)

You do know, I hope, that this computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.
Click to expand...
Click to expand...

Yup! I miss read your question.
=DateAdd("yyyy",5,[Purchased])
will return the date 5 years from the date purchased.

I don't know what you mean when you write "The ouput should be in
numbers not dates."

If you wish to have a query tell you to replace the computers, then
create a query with the necessary fields.

Add a new column.
TimeToReplace:IIf(DateAdd("yyyy",5,[Purchased]) >=
Date(),"Replace","Not Yet")
 
The reason why I'm saying that the output should be numbers not date is
because the purchased field is a date, but my replace column should be
numbers.

fredg said:
Fred,

thanks once again for the info., however I'm getting some negative numbers
that I'm not sure what they mean. for example: my desktop was purchased
2/3/2003, I should be ready for refresh 2/3/2008. I want this query to tell
me that I'm ready for refresh. The ouput should be in numbers not dates.

Thanks

fredg said:
On Wed, 24 Oct 2007 13:07:00 -0700, Naja wrote:

I need to create an expression for the following:

I have an age report that has to tell me which computer is ready to be
replaced. the computer has to be older than 5 years. so how should I write
the expression. In my table I have a field of purchased. Thanks

In a query?
Add a new column.

ReplaceDate:DateDiff("yyyy", [Purchased], Date()) -
IIF(Format([Purchased], "mmdd") > Format(Date(), "mmdd"), 1, 0)

Directly in a form or on a report?
Use an Unbound text control.
Set it's control source to:

= DateDiff("yyyy",[Purchased],Date())-IIf(Format([Purchased],"mmdd" ) >
Format(Date(), "mmdd"),1,0)

You do know, I hope, that this computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.
Click to expand...
Click to expand...

Yup! I miss read your question.
=DateAdd("yyyy",5,[Purchased])
will return the date 5 years from the date purchased.

I don't know what you mean when you write "The ouput should be in
numbers not dates."

If you wish to have a query tell you to replace the computers, then
create a query with the necessary fields.

Add a new column.
TimeToReplace:IIf(DateAdd("yyyy",5,[Purchased]) >=
Date(),"Replace","Not Yet")
Click to expand...
 
The reason why I'm saying that the output should be numbers not date is
because the purchased field is a date, but my replace column should be
numbers.
Click to expand...

WHAT numbers? What do the numbers mean, and where do you want them to come
from?

John W. Vinson [MVP]
 
Sorry that my messages are not that clear. Let me see if I can break it down
better.

I have a raw data that I download from an application.
This file contains information about when our desktop was deploy, who has
it, etc, etc.
my company implemented a project to replace old desktop that are 5 years or
oldest.
this data has a column that gives me on what date it was deployed.
So I need a column that tells me the number of years that this desktop has.

I hope this is more clear.
 
For this I would probably use the following expression and I wouldn't worry
about a high-degree of accuracy

Int((Date()-[Date Deployed]) / 365.25)

That formula will be accurate within a day or two of the number of years
since a computer has been deployed. If you want partial years then drop the
Int.

If you absolutely require the formula to be totally accurate in terms of
years, then you can use an express like the following to return the age in
years

DateDiff("yyyy",[Date Deployed],Date()) - Abs( Format(Date(),"mmdd") >
Format([Date Deployed],"mmdd") )

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks John I think you gave me what I was looking for. I will run the
report and present it to management and see if this is what they are looking
for.

John Spencer said:
For this I would probably use the following expression and I wouldn't worry
about a high-degree of accuracy

Int((Date()-[Date Deployed]) / 365.25)

That formula will be accurate within a day or two of the number of years
since a computer has been deployed. If you want partial years then drop the
Int.

If you absolutely require the formula to be totally accurate in terms of
years, then you can use an express like the following to return the age in
years

DateDiff("yyyy",[Date Deployed],Date()) - Abs( Format(Date(),"mmdd") >
Format([Date Deployed],"mmdd") )

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

naja said:
Sorry that my messages are not that clear. Let me see if I can break it
down
better.

I have a raw data that I download from an application.
This file contains information about when our desktop was deploy, who has
it, etc, etc.
my company implemented a project to replace old desktop that are 5 years
or
oldest.
this data has a column that gives me on what date it was deployed.
So I need a column that tells me the number of years that this desktop
has.

I hope this is more clear.
Click to expand...
Click to expand...
 
Back
Top