I have a date field "close date" that needs to be updated. It is contingent
upon the status of a preceding field "status".

When the status field is updated to Active, I want the close date field to
be cleared.

When the status field is updated to Closed, I was the close date filed to
enter today's date.

How can I do this by macro? Can this be accomplished with an After Update
command?

Mac,

The best way here is to make an Update Query to update your Close Date field
to Date(), and then you would use a macro with an OpenQuery action, in the
After Update event of the Status control on your form.

However, I would also say that on the basis of what you have told us so far,
the Status field itself can be derived from whether or not there is any data
in the Close Date field, so therefore there would possibly be a case for
removing the Status field from your table altogether, and calculate it in a
query or on your form or report whenever you need to know it for your data
operational purposes. Hope you understand what I am getting at here.

--
Steve Schapel, Microsoft Access MVP


"Mac" <(E-Mail Removed)> wrote in message
news:801DEA5B-7662-4100-A3BE-(E-Mail Removed)...
> I have a date field "close date" that needs to be updated. It is
> contingent
> upon the status of a preceding field "status".
>
> When the status field is updated to Active, I want the close date field to
> be cleared.
>
> When the status field is updated to Closed, I was the close date filed to
> enter today's date.
>
> How can I do this by macro? Can this be accomplished with an After Update
> command?

I was able to the Update Query where the Date() shows up when I change the
status from Active to Closed, but I still need to be able to have the date
field cleared out when the status changes from Closed to Active.

The purpose of this is to keep track of Active accounts v. Closed accounts.
If they're active, no need for a Close Date so this field needs to update to
a null status. I tried using the Update Query to replace the date with Null,
but that did nothing for me.

I'm sure ther's VBA code to accomplish this, but I'm mpt familiar with VBA
script and really do not know where to start with that. So for now, I'd like
to either use a Macro or a combo of Macros and Queries.

Thanks!
"Steve Schapel" wrote:

> Mac,
>
> The best way here is to make an Update Query to update your Close Date field
> to Date(), and then you would use a macro with an OpenQuery action, in the
> After Update event of the Status control on your form.
>
> However, I would also say that on the basis of what you have told us so far,
> the Status field itself can be derived from whether or not there is any data
> in the Close Date field, so therefore there would possibly be a case for
> removing the Status field from your table altogether, and calculate it in a
> query or on your form or report whenever you need to know it for your data
> operational purposes. Hope you understand what I am getting at here.
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> "Mac" <(E-Mail Removed)> wrote in message
> news:801DEA5B-7662-4100-A3BE-(E-Mail Removed)...
> > I have a date field "close date" that needs to be updated. It is
> > contingent
> > upon the status of a preceding field "status".
> >
> > When the status field is updated to Active, I want the close date field to
> > be cleared.
> >
> > When the status field is updated to Closed, I was the close date filed to
> > enter today's date.
> >
> > How can I do this by macro? Can this be accomplished with an After Update
> > command?
>
> .
>

Mac -

You should be able to update the date field to null - something like this:

UPDATE Table1 SET TableName.DateField = Null
WHERE (((Table1.keyfield)=<keyfieldFromForm.));

It should be very similar to your query that puts in the current date.
--
Daryl S


"Mac" wrote:

> I was able to the Update Query where the Date() shows up when I change the
> status from Active to Closed, but I still need to be able to have the date
> field cleared out when the status changes from Closed to Active.
>
> The purpose of this is to keep track of Active accounts v. Closed accounts.
> If they're active, no need for a Close Date so this field needs to update to
> a null status. I tried using the Update Query to replace the date with Null,
> but that did nothing for me.
>
> I'm sure ther's VBA code to accomplish this, but I'm mpt familiar with VBA
> script and really do not know where to start with that. So for now, I'd like
> to either use a Macro or a combo of Macros and Queries.
>
> Thanks!
> "Steve Schapel" wrote:
>
> > Mac,
> >
> > The best way here is to make an Update Query to update your Close Date field
> > to Date(), and then you would use a macro with an OpenQuery action, in the
> > After Update event of the Status control on your form.
> >
> > However, I would also say that on the basis of what you have told us so far,
> > the Status field itself can be derived from whether or not there is any data
> > in the Close Date field, so therefore there would possibly be a case for
> > removing the Status field from your table altogether, and calculate it in a
> > query or on your form or report whenever you need to know it for your data
> > operational purposes. Hope you understand what I am getting at here.
> >
> > --
> > Steve Schapel, Microsoft Access MVP
> >
> >
> > "Mac" <(E-Mail Removed)> wrote in message
> > news:801DEA5B-7662-4100-A3BE-(E-Mail Removed)...
> > > I have a date field "close date" that needs to be updated. It is
> > > contingent
> > > upon the status of a preceding field "status".
> > >
> > > When the status field is updated to Active, I want the close date field to
> > > be cleared.
> > >
> > > When the status field is updated to Closed, I was the close date filed to
> > > enter today's date.
> > >
> > > How can I do this by macro? Can this be accomplished with an After Update
> > > command?
> >
> > .
> >

I don't understand the "WHERE" command, this looks like SQL and I'm not very
familiar with that either, but I am trying to learn it too. How would it
look in design view in a query?

"Daryl S" wrote:

> Mac -
>
> You should be able to update the date field to null - something like this:
>
> UPDATE Table1 SET TableName.DateField = Null
> WHERE (((Table1.keyfield)=<keyfieldFromForm.));
>
> It should be very similar to your query that puts in the current date.
> --
> Daryl S
>
>
> "Mac" wrote:
>
> > I was able to the Update Query where the Date() shows up when I change the
> > status from Active to Closed, but I still need to be able to have the date
> > field cleared out when the status changes from Closed to Active.
> >
> > The purpose of this is to keep track of Active accounts v. Closed accounts.
> > If they're active, no need for a Close Date so this field needs to update to
> > a null status. I tried using the Update Query to replace the date with Null,
> > but that did nothing for me.
> >
> > I'm sure ther's VBA code to accomplish this, but I'm mpt familiar with VBA
> > script and really do not know where to start with that. So for now, I'd like
> > to either use a Macro or a combo of Macros and Queries.
> >
> > Thanks!
> > "Steve Schapel" wrote:
> >
> > > Mac,
> > >
> > > The best way here is to make an Update Query to update your Close Date field
> > > to Date(), and then you would use a macro with an OpenQuery action, in the
> > > After Update event of the Status control on your form.
> > >
> > > However, I would also say that on the basis of what you have told us so far,
> > > the Status field itself can be derived from whether or not there is any data
> > > in the Close Date field, so therefore there would possibly be a case for
> > > removing the Status field from your table altogether, and calculate it in a
> > > query or on your form or report whenever you need to know it for your data
> > > operational purposes. Hope you understand what I am getting at here.
> > >
> > > --
> > > Steve Schapel, Microsoft Access MVP
> > >
> > >
> > > "Mac" <(E-Mail Removed)> wrote in message
> > > news:801DEA5B-7662-4100-A3BE-(E-Mail Removed)...
> > > > I have a date field "close date" that needs to be updated. It is
> > > > contingent
> > > > upon the status of a preceding field "status".
> > > >
> > > > When the status field is updated to Active, I want the close date field to
> > > > be cleared.
> > > >
> > > > When the status field is updated to Closed, I was the close date filed to
> > > > enter today's date.
> > > >
> > > > How can I do this by macro? Can this be accomplished with an After Update
> > > > command?
> > >
> > > .
> > >

Mac,

If you successfully got an Update Query to write the current date Date()
into the Close Date field, then you just need to do exactly the same thing
to clear it, except the query would have Null in the Update To row of the
query design grid, instead of Date(). Is that what you tried?

By the way, I won't pursue the issue of the validity of the Status field at
the moment, but in fact there is a database design flaw here.

--
Steve Schapel, Microsoft Access MVP


"Mac" <(E-Mail Removed)> wrote in message
news:47BA5BC8-43F9-4B18-93EB-(E-Mail Removed)...
> I was able to the Update Query where the Date() shows up when I change the
> status from Active to Closed, but I still need to be able to have the date
> field cleared out when the status changes from Closed to Active.
>
> The purpose of this is to keep track of Active accounts v. Closed
> accounts.
> If they're active, no need for a Close Date so this field needs to update
> to
> a null status. I tried using the Update Query to replace the date with
> Null,
> but that did nothing for me.
>
> I'm sure ther's VBA code to accomplish this, but I'm mpt familiar with VBA
> script and really do not know where to start with that. So for now, I'd
> like
> to either use a Macro or a combo of Macros and Queries.
>

If there's a macro I could use so that I wouldn't have to keep track of
another query or accept "yes, I want to update these fields" every time I
update a record.
like...

after updating the status field from either active to closed this is what I
had in mind, but don't know how to do it.

if: Status = Closed
and: Close Date = Null
then: Update Close Date = DATE()
if not: Closed
then: next...

if: Status = Suspended
and: Close Date = Null
then: Update Close Date to DATE()
if not: Suspended
then: next...

if: Status = Closed
and: Close Date = Not Null (because account was previously suspended, but
now closed)
then: update Close Date to DATE()
if not: Closed
then: next...

if: Status = Active
and: Close date = Null
then: next...

if: Status = Active
and: Close Date = Not Null
then: update Closed Date to Null


Does this help?




"Daryl S" wrote:

> Mac -
>
> You should be able to update the date field to null - something like this:
>
> UPDATE Table1 SET TableName.DateField = Null
> WHERE (((Table1.keyfield)=<keyfieldFromForm.));
>
> It should be very similar to your query that puts in the current date.
> --
> Daryl S
>
>
> "Mac" wrote:
>
> > I was able to the Update Query where the Date() shows up when I change the
> > status from Active to Closed, but I still need to be able to have the date
> > field cleared out when the status changes from Closed to Active.
> >
> > The purpose of this is to keep track of Active accounts v. Closed accounts.
> > If they're active, no need for a Close Date so this field needs to update to
> > a null status. I tried using the Update Query to replace the date with Null,
> > but that did nothing for me.
> >
> > I'm sure ther's VBA code to accomplish this, but I'm mpt familiar with VBA
> > script and really do not know where to start with that. So for now, I'd like
> > to either use a Macro or a combo of Macros and Queries.
> >
> > Thanks!
> > "Steve Schapel" wrote:
> >
> > > Mac,
> > >
> > > The best way here is to make an Update Query to update your Close Date field
> > > to Date(), and then you would use a macro with an OpenQuery action, in the
> > > After Update event of the Status control on your form.
> > >
> > > However, I would also say that on the basis of what you have told us so far,
> > > the Status field itself can be derived from whether or not there is any data
> > > in the Close Date field, so therefore there would possibly be a case for
> > > removing the Status field from your table altogether, and calculate it in a
> > > query or on your form or report whenever you need to know it for your data
> > > operational purposes. Hope you understand what I am getting at here.
> > >
> > > --
> > > Steve Schapel, Microsoft Access MVP
> > >
> > >
> > > "Mac" <(E-Mail Removed)> wrote in message
> > > news:801DEA5B-7662-4100-A3BE-(E-Mail Removed)...
> > > > I have a date field "close date" that needs to be updated. It is
> > > > contingent
> > > > upon the status of a preceding field "status".
> > > >
> > > > When the status field is updated to Active, I want the close date field to
> > > > be cleared.
> > > >
> > > > When the status field is updated to Closed, I was the close date filed to
> > > > enter today's date.
> > > >
> > > > How can I do this by macro? Can this be accomplished with an After Update
> > > > command?
> > >
> > > .
> > >

Mac -

You can switch most queries (not pass-thru or UNION queries) back and forth
between design view and SQL view. That means you can copy/paste the SQL into
the SQL View window, and if all the tablenames and fieldnames are correct,
you can change to design view and see it the way you are used to. If there
is an error, Access is pretty good at saying so. They are usually typos in
fieldnames or tablenames.

It is much easier for us to understand the SQL statement then to try to
describe a query in design view.

To get into a blank SQL View, open a new query without selecting any tables,
then switch to SQL View. Then paste in the query I gave you, but substitue
your table and field names. Then switch to design view so you can see what
it will do.
--
Daryl S


"Mac" wrote:

> I don't understand the "WHERE" command, this looks like SQL and I'm not very
> familiar with that either, but I am trying to learn it too. How would it
> look in design view in a query?
>
> "Daryl S" wrote:
>
> > Mac -
> >
> > You should be able to update the date field to null - something like this:
> >
> > UPDATE TableName SET TableName.DateField = Null
> > WHERE (((TableName.keyfield)=<keyfieldFromForm.));
> >
> > It should be very similar to your query that puts in the current date.
> > --
> > Daryl S
> >
> >
> > "Mac" wrote:
> >
> > > I was able to the Update Query where the Date() shows up when I change the
> > > status from Active to Closed, but I still need to be able to have the date
> > > field cleared out when the status changes from Closed to Active.
> > >
> > > The purpose of this is to keep track of Active accounts v. Closed accounts.
> > > If they're active, no need for a Close Date so this field needs to update to
> > > a null status. I tried using the Update Query to replace the date with Null,
> > > but that did nothing for me.
> > >
> > > I'm sure ther's VBA code to accomplish this, but I'm mpt familiar with VBA
> > > script and really do not know where to start with that. So for now, I'd like
> > > to either use a Macro or a combo of Macros and Queries.
> > >
> > > Thanks!
> > > "Steve Schapel" wrote:
> > >
> > > > Mac,
> > > >
> > > > The best way here is to make an Update Query to update your Close Date field
> > > > to Date(), and then you would use a macro with an OpenQuery action, in the
> > > > After Update event of the Status control on your form.
> > > >
> > > > However, I would also say that on the basis of what you have told us so far,
> > > > the Status field itself can be derived from whether or not there is any data
> > > > in the Close Date field, so therefore there would possibly be a case for
> > > > removing the Status field from your table altogether, and calculate it in a
> > > > query or on your form or report whenever you need to know it for your data
> > > > operational purposes. Hope you understand what I am getting at here.
> > > >
> > > > --
> > > > Steve Schapel, Microsoft Access MVP
> > > >
> > > >
> > > > "Mac" <(E-Mail Removed)> wrote in message
> > > > news:801DEA5B-7662-4100-A3BE-(E-Mail Removed)...
> > > > > I have a date field "close date" that needs to be updated. It is
> > > > > contingent
> > > > > upon the status of a preceding field "status".
> > > > >
> > > > > When the status field is updated to Active, I want the close date field to
> > > > > be cleared.
> > > > >
> > > > > When the status field is updated to Closed, I was the close date filed to
> > > > > enter today's date.
> > > > >
> > > > > How can I do this by macro? Can this be accomplished with an After Update
> > > > > command?
> > > >
> > > > .
> > > >

Mac -

This is better done in code than in a macro. Here is what you would want
to put into the code for your BeforeUpdate event on the Status field (change
the "Status" and "CloseDate" fields to match the names on your form):

Select Case Me.Status
Case "Closed" 'Always set to today's date
Me.CloseDate = Date
Case "Suspended" 'Set to today's date if it is null
If IsNull(Me.CloseDate) Then
Me.CloseDate = Date
End If
Case "Active" 'Always set to Null
Me.CloseDate = Null
Case Else
MsgBox "Unexpected Status: " & Me.Status
End Select


--
Daryl S


"Mac" wrote:

> If there's a macro I could use so that I wouldn't have to keep track of
> another query or accept "yes, I want to update these fields" every time I
> update a record.
> like...
>
> after updating the status field from either active to closed this is what I
> had in mind, but don't know how to do it.
>
> if: Status = Closed
> and: Close Date = Null
> then: Update Close Date = DATE()
> if not: Closed
> then: next...
>
> if: Status = Suspended
> and: Close Date = Null
> then: Update Close Date to DATE()
> if not: Suspended
> then: next...
>
> if: Status = Closed
> and: Close Date = Not Null (because account was previously suspended, but
> now closed)
> then: update Close Date to DATE()
> if not: Closed
> then: next...
>
> if: Status = Active
> and: Close date = Null
> then: next...
>
> if: Status = Active
> and: Close Date = Not Null
> then: update Closed Date to Null
>
>
> Does this help?
>
>
>
>
> "Daryl S" wrote:
>
> > Mac -
> >
> > You should be able to update the date field to null - something like this:
> >
> > UPDATE Table1 SET TableName.DateField = Null
> > WHERE (((Table1.keyfield)=<keyfieldFromForm.));
> >
> > It should be very similar to your query that puts in the current date.
> > --
> > Daryl S
> >
> >
> > "Mac" wrote:
> >
> > > I was able to the Update Query where the Date() shows up when I change the
> > > status from Active to Closed, but I still need to be able to have the date
> > > field cleared out when the status changes from Closed to Active.
> > >
> > > The purpose of this is to keep track of Active accounts v. Closed accounts.
> > > If they're active, no need for a Close Date so this field needs to update to
> > > a null status. I tried using the Update Query to replace the date with Null,
> > > but that did nothing for me.
> > >
> > > I'm sure ther's VBA code to accomplish this, but I'm mpt familiar with VBA
> > > script and really do not know where to start with that. So for now, I'd like
> > > to either use a Macro or a combo of Macros and Queries.
> > >
> > > Thanks!
> > > "Steve Schapel" wrote:
> > >
> > > > Mac,
> > > >
> > > > The best way here is to make an Update Query to update your Close Date field
> > > > to Date(), and then you would use a macro with an OpenQuery action, in the
> > > > After Update event of the Status control on your form.
> > > >
> > > > However, I would also say that on the basis of what you have told us so far,
> > > > the Status field itself can be derived from whether or not there is any data
> > > > in the Close Date field, so therefore there would possibly be a case for
> > > > removing the Status field from your table altogether, and calculate it in a
> > > > query or on your form or report whenever you need to know it for your data
> > > > operational purposes. Hope you understand what I am getting at here.
> > > >
> > > > --
> > > > Steve Schapel, Microsoft Access MVP
> > > >
> > > >
> > > > "Mac" <(E-Mail Removed)> wrote in message
> > > > news:801DEA5B-7662-4100-A3BE-(E-Mail Removed)...
> > > > > I have a date field "close date" that needs to be updated. It is
> > > > > contingent
> > > > > upon the status of a preceding field "status".
> > > > >
> > > > > When the status field is updated to Active, I want the close date field to
> > > > > be cleared.
> > > > >
> > > > > When the status field is updated to Closed, I was the close date filed to
> > > > > enter today's date.
> > > > >
> > > > > How can I do this by macro? Can this be accomplished with an After Update
> > > > > command?
> > > >
> > > > .
> > > >

what do you mean flaw? what's wrong with it?

"Steve Schapel" wrote:

> Mac,
>
> If you successfully got an Update Query to write the current date Date()
> into the Close Date field, then you just need to do exactly the same thing
> to clear it, except the query would have Null in the Update To row of the
> query design grid, instead of Date(). Is that what you tried?
>
> By the way, I won't pursue the issue of the validity of the Status field at
> the moment, but in fact there is a database design flaw here.
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> "Mac" <(E-Mail Removed)> wrote in message
> news:47BA5BC8-43F9-4B18-93EB-(E-Mail Removed)...
> > I was able to the Update Query where the Date() shows up when I change the
> > status from Active to Closed, but I still need to be able to have the date
> > field cleared out when the status changes from Closed to Active.
> >
> > The purpose of this is to keep track of Active accounts v. Closed
> > accounts.
> > If they're active, no need for a Close Date so this field needs to update
> > to
> > a null status. I tried using the Update Query to replace the date with
> > Null,
> > but that did nothing for me.
> >
> > I'm sure ther's VBA code to accomplish this, but I'm mpt familiar with VBA
> > script and really do not know where to start with that. So for now, I'd
> > like
> > to either use a Macro or a combo of Macros and Queries.
> >
>
>
> .
>