Control the value of one field based on date in another field

[Katherine R]

In a continuous form, I would like to set the value of one field based on a
date in another field. The fields on the form are [LicenseNumber],
[ApplicationType], [IssueDate], [ExpirationDate], and [ApplicationStatus].
Licenses are required to be renewed on Jan. 1st of each year and expire on
(or sometimes before) Dec. 31st of each year. When the expiration date is
equal to or greater than the current date (plus 24 hrs?), I would like the
application status to change from “Active†to “Closedâ€. The default value of
the application status field is already set to “Activeâ€. Can anyone help me
with this and give me an example of the code? Thanks much!

 

[ruralguy via AccessMonster.com]

Hi Katherine,
You actually have a field that is a calculation and generally should be
avoided. Use a query on the table and put the following as one of the fields.
It is AIR CODE so I might have a typo.

Status: = IIF(DateDiff("d",[ExpirationDate]+1,Date()) > 0, "Active","Closed")

It calculates every time you run the query and looks like a real field to the
rest of the world. Queries are *very* powerful and the real power behind
Access.

In a continuous form, I would like to set the value of one field based on a
date in another field. The fields on the form are [LicenseNumber],
[ApplicationType], [IssueDate], [ExpirationDate], and [ApplicationStatus].
Licenses are required to be renewed on Jan. 1st of each year and expire on
(or sometimes before) Dec. 31st of each year. When the expiration date is
equal to or greater than the current date (plus 24 hrs?), I would like the
application status to change from “Active†to “Closedâ€. The default value of
the application status field is already set to “Activeâ€. Can anyone help me
with this and give me an example of the code? Thanks much!

 

[Tony Vrolyk]

I guess the question is when do you want it to change the status?

If you to this on the form, you really can only affect one record at a time.
That requires that the focus change at least momentarily to each record that
is out of date. If you are certain that the records will all be 'touched'
then you could do something OnCurrent like

If Me.ExpirationDate < Date()
Me.ApplicationStatus = "Closed"
End If

This would just evaluate on the date not exactly 24 hours so a record could
become Closed at 12:00 AM. If you don't literally need an exact 24 hours you
will find this a lot easier. otherwise you will need to do a more detail
date calculation. There you will run into problems if the ExpirationDate is
just a date and does not include exact time. If it is just a date then the
time will default to 00:00:00 (Midnight).

I am also guessing you would want to add this code to the AfterUpdate of the
ExpirationDate control so that if the user changes the date to an old one it
immediately updates the Status.

On the otherhand if the data needs to be updated daily but not every record
is touched you might consider doing a separate function that will run an
update query for all out-of-date records. That function could be called
everytime the database opens, or overnight using the Windows Scheduled
Tasks.

Hope that help
Tony

 

[Tony Vrolyk]

Katherine -

I hadn't thought of it that way and it does make sense.

However you could have a situation where Status can be changed for other
reasons besides just the expiration date. In this case it looks like the
poster is trying to track application status. It could be that an
application can be closed before the expiration date has passed for instance
of the applicaion was approved, declined or cancelled for other reasons. In
that case I think a field for Status is in order. Or there may be other
statuses in addtion to Active or Closed that have other meanings.

However your suggestion is excellent. The poster will have to decide what is
most appropriate to their situation.

Tony

Hi Katherine,
You actually have a field that is a calculation and generally should be
avoided. Use a query on the table and put the following as one of the
fields.
It is AIR CODE so I might have a typo.

Status: = IIF(DateDiff("d",[ExpirationDate]+1,Date()) > 0,
"Active","Closed")

It calculates every time you run the query and looks like a real field to
the
rest of the world. Queries are *very* powerful and the real power behind
Access.

In a continuous form, I would like to set the value of one field based on
a
date in another field. The fields on the form are [LicenseNumber],
[ApplicationType], [IssueDate], [ExpirationDate], and [ApplicationStatus].
Licenses are required to be renewed on Jan. 1st of each year and expire on
(or sometimes before) Dec. 31st of each year. When the expiration date is
equal to or greater than the current date (plus 24 hrs?), I would like the
application status to change from "Active" to "Closed". The default value
of
the application status field is already set to "Active". Can anyone help
me
with this and give me an example of the code? Thanks much!

 

[Tony Vrolyk]

Sorry, my last post should have been directed to ruralguy but of course for
your benefit as well Katherine.

Tony

Katherine -

I hadn't thought of it that way and it does make sense.

However you could have a situation where Status can be changed for other
reasons besides just the expiration date. In this case it looks like the
poster is trying to track application status. It could be that an
application can be closed before the expiration date has passed for
instance of the applicaion was approved, declined or cancelled for other
reasons. In that case I think a field for Status is in order. Or there may
be other statuses in addtion to Active or Closed that have other meanings.

However your suggestion is excellent. The poster will have to decide what
is most appropriate to their situation.

Tony

Hi Katherine,
You actually have a field that is a calculation and generally should be
avoided. Use a query on the table and put the following as one of the
fields.
It is AIR CODE so I might have a typo.

Status: = IIF(DateDiff("d",[ExpirationDate]+1,Date()) > 0,
"Active","Closed")

It calculates every time you run the query and looks like a real field to
the
rest of the world. Queries are *very* powerful and the real power behind
Access.

In a continuous form, I would like to set the value of one field based on
a
date in another field. The fields on the form are [LicenseNumber],
[ApplicationType], [IssueDate], [ExpirationDate], and
[ApplicationStatus].
Licenses are required to be renewed on Jan. 1st of each year and expire
on
(or sometimes before) Dec. 31st of each year. When the expiration date
is
equal to or greater than the current date (plus 24 hrs?), I would like
the
application status to change from "Active" to "Closed". The default
value of
the application status field is already set to "Active". Can anyone help
me
with this and give me an example of the code? Thanks much!

 

[ruralguy via AccessMonster.com]

Hi Tony,
You just listed a few of the reasons why it is not a very good idea to store
values that can be calculated. You are correct in that Katherine will have
to decide what is best in this situation.

I guess the question is when do you want it to change the status?

If you to this on the form, you really can only affect one record at a time.
That requires that the focus change at least momentarily to each record that
is out of date. If you are certain that the records will all be 'touched'
then you could do something OnCurrent like

If Me.ExpirationDate < Date()
Me.ApplicationStatus = "Closed"
End If

This would just evaluate on the date not exactly 24 hours so a record could
become Closed at 12:00 AM. If you don't literally need an exact 24 hours you
will find this a lot easier. otherwise you will need to do a more detail
date calculation. There you will run into problems if the ExpirationDate is
just a date and does not include exact time. If it is just a date then the
time will default to 00:00:00 (Midnight).

I am also guessing you would want to add this code to the AfterUpdate of the
ExpirationDate control so that if the user changes the date to an old one it
immediately updates the Status.

On the otherhand if the data needs to be updated daily but not every record
is touched you might consider doing a separate function that will run an
update query for all out-of-date records. That function could be called
everytime the database opens, or overnight using the Windows Scheduled
Tasks.

Hope that help
Tony

In a continuous form, I would like to set the value of one field based on
a

[quoted text clipped - 8 lines]

me
with this and give me an example of the code? Thanks much!

 

[Katherine R]

I think I understand most of what you and Tony are saying and I think the
query is the better way to go. I'm about to ask what is probably a stupid
question: I already have reports in my database that are based on other
queries that include the 'old' ApplicationStatus field. In two of these
queries I've entered criteria in the that field to print, for example, a list
of all "Active" applications. Will I need to modify these queries?

Also, how do I get the query to run automatically every time the database
opens?

Thanks.

Hi Katherine,
You actually have a field that is a calculation and generally should be
avoided. Use a query on the table and put the following as one of the fields.
It is AIR CODE so I might have a typo.

Status: = IIF(DateDiff("d",[ExpirationDate]+1,Date()) > 0, "Active","Closed")

It calculates every time you run the query and looks like a real field to the
rest of the world. Queries are *very* powerful and the real power behind
Access.

In a continuous form, I would like to set the value of one field based on a
date in another field. The fields on the form are [LicenseNumber],
[ApplicationType], [IssueDate], [ExpirationDate], and [ApplicationStatus].
Licenses are required to be renewed on Jan. 1st of each year and expire on
(or sometimes before) Dec. 31st of each year. When the expiration date is
equal to or greater than the current date (plus 24 hrs?), I would like the
application status to change from “Active†to “Closedâ€. The default value of
the application status field is already set to “Activeâ€. Can anyone help me
with this and give me an example of the code? Thanks much!

 

[Tony Vrolyk]

Yea. You could do a couple things.

If you need to actualy output the words "Active" or "Closed" then will need
to create the user defined field as ruralguuy described. And if necessary
set the criteria to "Active" or "Closed"

Or if you don't need to output the word (the report already has that in the
title for instance) you could just use <Date() as the criteria under the
ExpirationDate Field

Tony

I think I understand most of what you and Tony are saying and I think the
query is the better way to go. I'm about to ask what is probably a stupid
question: I already have reports in my database that are based on other
queries that include the 'old' ApplicationStatus field. In two of these
queries I've entered criteria in the that field to print, for example, a
list
of all "Active" applications. Will I need to modify these queries?

Also, how do I get the query to run automatically every time the database
opens?

Thanks.

Hi Katherine,
You actually have a field that is a calculation and generally should be
avoided. Use a query on the table and put the following as one of the
fields.
It is AIR CODE so I might have a typo.

Status: = IIF(DateDiff("d",[ExpirationDate]+1,Date()) > 0,
"Active","Closed")

It calculates every time you run the query and looks like a real field to
the
rest of the world. Queries are *very* powerful and the real power behind
Access.

In a continuous form, I would like to set the value of one field based
on a
date in another field. The fields on the form are [LicenseNumber],
[ApplicationType], [IssueDate], [ExpirationDate], and
[ApplicationStatus].
Licenses are required to be renewed on Jan. 1st of each year and expire
on
(or sometimes before) Dec. 31st of each year. When the expiration date
is
equal to or greater than the current date (plus 24 hrs?), I would like
the
application status to change from "Active" to "Closed". The default
value of
the application status field is already set to "Active". Can anyone
help me
with this and give me an example of the code? Thanks much!

 

[ruralguy via AccessMonster.com]

Hi Katherine,
Go ahead and create a New Query and don't include the [ApplicationStatus]
field and instead create your own calculated field as we described and name
it ApplicationStatus. Then change your other queries to use this new query
rather than the table and they will never know the difference.

I think I understand most of what you and Tony are saying and I think the
query is the better way to go. I'm about to ask what is probably a stupid
question: I already have reports in my database that are based on other
queries that include the 'old' ApplicationStatus field. In two of these
queries I've entered criteria in the that field to print, for example, a list
of all "Active" applications. Will I need to modify these queries?

Also, how do I get the query to run automatically every time the database
opens?

Thanks.

Hi Katherine,
You actually have a field that is a calculation and generally should be

[quoted text clipped - 16 lines]

 

[Katherine R]

RG,

I've done away with the [AppliationStatus] field and I'm using the
calculated field instead. All querys, forms, and reports are working fine
now. I did modify the statement in the query slightly. I was confused
(again) in my initial postings and what I really want is that the status
change to "Closed" at the end of the expiration date. I changed the
statement to: Status: IIf([DateOfExpiration]>Date(),"Active","Closed")
Thanks so much for your help - I would not have known on my own to use a
query and calculated fields instead of fields from a table.

Is there a way to get the query to run automatically every time the database
is opened?

Thanks.

Hi Katherine,
Go ahead and create a New Query and don't include the [ApplicationStatus]
field and instead create your own calculated field as we described and name
it ApplicationStatus. Then change your other queries to use this new query
rather than the table and they will never know the difference.

I think I understand most of what you and Tony are saying and I think the
query is the better way to go. I'm about to ask what is probably a stupid
question: I already have reports in my database that are based on other
queries that include the 'old' ApplicationStatus field. In two of these
queries I've entered criteria in the that field to print, for example, a list
of all "Active" applications. Will I need to modify these queries?

Also, how do I get the query to run automatically every time the database
opens?

Thanks.

Hi Katherine,
You actually have a field that is a calculation and generally should be

[quoted text clipped - 16 lines]

the application status field is already set to “Activeâ€. Can anyone help me
with this and give me an example of the code? Thanks much!

 

[ruralguy via AccessMonster.com]

Why run it automatically when your field is calculated *every* time. No
fields are being chanced so no update is necessary. As for running things
when the db is opened there are at least a couple of options. Open a form
automatically and use the OnLoad event to run anything you want. Create an
AutoExec macro. It will execute when the db loads.

RG,

I've done away with the [AppliationStatus] field and I'm using the
calculated field instead. All querys, forms, and reports are working fine
now. I did modify the statement in the query slightly. I was confused
(again) in my initial postings and what I really want is that the status
change to "Closed" at the end of the expiration date. I changed the
statement to: Status: IIf([DateOfExpiration]>Date(),"Active","Closed")
Thanks so much for your help - I would not have known on my own to use a
query and calculated fields instead of fields from a table.

Is there a way to get the query to run automatically every time the database
is opened?

Thanks.

Hi Katherine,
Go ahead and create a New Query and don't include the [ApplicationStatus]

[quoted text clipped - 19 lines]

 

[Katherine R]

You're right, of course. Thanks again.

Why run it automatically when your field is calculated *every* time. No
fields are being chanced so no update is necessary. As for running things
when the db is opened there are at least a couple of options. Open a form
automatically and use the OnLoad event to run anything you want. Create an
AutoExec macro. It will execute when the db loads.

RG,

I've done away with the [AppliationStatus] field and I'm using the
calculated field instead. All querys, forms, and reports are working fine
now. I did modify the statement in the query slightly. I was confused
(again) in my initial postings and what I really want is that the status
change to "Closed" at the end of the expiration date. I changed the
statement to: Status: IIf([DateOfExpiration]>Date(),"Active","Closed")
Thanks so much for your help - I would not have known on my own to use a
query and calculated fields instead of fields from a table.

Is there a way to get the query to run automatically every time the database
is opened?

Thanks.

Hi Katherine,
Go ahead and create a New Query and don't include the [ApplicationStatus]

[quoted text clipped - 19 lines]

the application status field is already set to “Activeâ€. Can anyone help me
with this and give me an example of the code? Thanks much!