Update Query that will check Status.

[Alchemist61]

Hi Guys,
I would greatly appreciated some help in building a Query that would check a
Training Date Column and return the results in another Column that if the
Training Date is more than one year ago, then it will return a value as DUE
otherwise OK. Please make it simple for a beginner.
Thanks
R.J

 

[John Spencer]

Use an expression in a query that looks like the following.

Field: IsDue: IIF([Training Date]<DateAdd("yyyy",-1,Date()), "Due","OK")



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

 

[Alchemist61]

Hi John,
Thanks for your reply. I created a new field in the table and name it IsDue
then in the Criteria field in the Query, I enter IIF([Training
Date]<DateAdd("yyyy",-1,Date()), "Due","OK")
but nothing happens. Am I doing something wrong?
Thanks

Use an expression in a query that looks like the following.

Field: IsDue: IIF([Training Date]<DateAdd("yyyy",-1,Date()), "Due","OK")



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

Hi Guys,
I would greatly appreciated some help in building a Query that would check a
Training Date Column and return the results in another Column that if the
Training Date is more than one year ago, then it will return a value as DUE
otherwise OK. Please make it simple for a beginner.
Thanks
R.J

 

[KARL DEWEY]

Am I doing something wrong?
Yes!
The field 'IsDue' is a calculated field in your query -- not a field in the
table -- not criteria.
Open your select query in design view. Scroll to grid to the right until a
blank column appears. In the Field row of the grid paste the calculated field
IsDue: IIF([Training Date]<DateAdd("yyyy",-1,Date()), "Due","OK")
and save.
--
KARL DEWEY
Build a little - Test a little

Hi John,
Thanks for your reply. I created a new field in the table and name it IsDue
then in the Criteria field in the Query, I enter IIF([Training
Date]<DateAdd("yyyy",-1,Date()), "Due","OK")
but nothing happens. Am I doing something wrong?
Thanks

Use an expression in a query that looks like the following.

Field: IsDue: IIF([Training Date]<DateAdd("yyyy",-1,Date()), "Due","OK")



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

Hi Guys,
I would greatly appreciated some help in building a Query that would check a
Training Date Column and return the results in another Column that if the
Training Date is more than one year ago, then it will return a value as DUE
otherwise OK. Please make it simple for a beginner.
Thanks
R.J

 

[John Spencer]

That is a calculated field and the entire expression belongs in the
"Field cell" of a query. I must have mislead you. You would not create
a new field in the table.


Field: IsDue: IIF([Training Date]<DateAdd("yyyy",-1,Date()), "Due","OK")
Criteria: Blank

IsDue is a name given to the result. If you don't assign a name to a
calculated field, Access will. Then you will end up with something like

Field: Expr1: IIF([Training Date]<DateAdd("yyyy",-1,Date()), "Due","OK")
Criteria: Blank

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

Hi John,
Thanks for your reply. I created a new field in the table and name it IsDue
then in the Criteria field in the Query, I enter IIF([Training
Date]<DateAdd("yyyy",-1,Date()), "Due","OK")
but nothing happens. Am I doing something wrong?
Thanks

Use an expression in a query that looks like the following.

Field: IsDue: IIF([Training Date]<DateAdd("yyyy",-1,Date()), "Due","OK")



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

Hi Guys,
I would greatly appreciated some help in building a Query that would check a
Training Date Column and return the results in another Column that if the
Training Date is more than one year ago, then it will return a value as DUE
otherwise OK. Please make it simple for a beginner.
Thanks
R.J

 

[Alchemist61]

Thank you very much Guys.
works perfectly.

Yes!
The field 'IsDue' is a calculated field in your query -- not a field in the
table -- not criteria.
Open your select query in design view. Scroll to grid to the right until a
blank column appears. In the Field row of the grid paste the calculated field
IsDue: IIF([Training Date]<DateAdd("yyyy",-1,Date()), "Due","OK")
and save.
--
KARL DEWEY
Build a little - Test a little

Hi John,
Thanks for your reply. I created a new field in the table and name it IsDue
then in the Criteria field in the Query, I enter IIF([Training
Date]<DateAdd("yyyy",-1,Date()), "Due","OK")
but nothing happens. Am I doing something wrong?
Thanks

Use an expression in a query that looks like the following.

Field: IsDue: IIF([Training Date]<DateAdd("yyyy",-1,Date()), "Due","OK")



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

Alchemist61 wrote:
Hi Guys,
I would greatly appreciated some help in building a Query that would check a
Training Date Column and return the results in another Column that if the
Training Date is more than one year ago, then it will return a value as DUE
otherwise OK. Please make it simple for a beginner.
Thanks
R.J

 

[Alchemist61]

You did not mislead me. It was my lack of understanding.
Thanks very much.

That is a calculated field and the entire expression belongs in the
"Field cell" of a query. I must have mislead you. You would not create
a new field in the table.


Field: IsDue: IIF([Training Date]<DateAdd("yyyy",-1,Date()), "Due","OK")
Criteria: Blank

IsDue is a name given to the result. If you don't assign a name to a
calculated field, Access will. Then you will end up with something like

Field: Expr1: IIF([Training Date]<DateAdd("yyyy",-1,Date()), "Due","OK")
Criteria: Blank

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

Hi John,
Thanks for your reply. I created a new field in the table and name it IsDue
then in the Criteria field in the Query, I enter IIF([Training
Date]<DateAdd("yyyy",-1,Date()), "Due","OK")
but nothing happens. Am I doing something wrong?
Thanks

Use an expression in a query that looks like the following.

Field: IsDue: IIF([Training Date]<DateAdd("yyyy",-1,Date()), "Due","OK")



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

Alchemist61 wrote:
Hi Guys,
I would greatly appreciated some help in building a Query that would check a
Training Date Column and return the results in another Column that if the
Training Date is more than one year ago, then it will return a value as DUE
otherwise OK. Please make it simple for a beginner.
Thanks
R.J