Clear a date field using a macro

[Mac]

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?

 

[Steve Schapel]

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.

 

[Mac]

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!

 

[Daryl S]

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.

 

[Mac]

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?

 

[Steve Schapel]

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.

 

[Mac]

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]

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 -

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

 

[Mac]

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

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]

would I attach this to the status field, or the date field?

 

[Mac]

Nevermind! YOU DID IT!! ok, now how do I learn this code? This is
fascinating!!

 

[Daryl S]

Mac -

Glad it is working for you.

This is VBA (Visual Basic ) code, and can be used with most Microsoft
Applications (e.g. Excel, Word, etc.). If you have never used it, then there
are lots of ways to learn - classes, books, tutorials, etc. There are tons
of free code samples on the internet, too. If you have not done any
programming, I would suggest a class so you get an overview of the language
and the constructs, and for Access, what objects and methods you will most
likely add code to. Classes are taught in many formats, depending on your
learning style, budget, and timing - from community colleges, on-line
classes, professional training, videos, books, etc.

If you have done programming, and just need help with syntax and the
language itself, then you can use the VBA Help in Access (pull up help from a
code window rather than from Access, which gives you Access Help). Lots of
code samples in these postings, and links to other code sites.

Good luck!