On Change Enter Date

[Jody]

I am working on a db that was developed in 2003 but I am now using 2007.
This db tracks nonconformancies and there is a "date initiated" but not a
"date closed". I am charged with building a report that shows how long
something was "on hold" (i.e. the duration of the job from initiation to
close). There is a combo box with a status of "Open" and "Closed" in which
"Open" is the default value and the user selects "Closed" when the job is
complete.

Here is my problem: I need to have the "closed date" (that I added to the
table) automatically populate with the date the person changed the status. I
initially built an update query that will set the date to =Date() and this
works great for setting ALL of the dates at one time, but I want it to only
affect the record being put in the "Closed" status. I attempted to do this
by creating an Event Procedure to run OnChange attached to the Status combo
box as "doCmd.OpenQuery, DateClosed" but I had no success with this.

Am I going about this all wrong?

 

[Jerry Whittle]

Yes. You shouldn't have an Open/Closed field any more. If you have a Closed
date field, that's all you need. If there is a date in it, it's Closed. Null
= Open.

Of course you may need to go back and populate the Closed cases with a date.

 

[Larry Daugherty]

I recommend two text boxes for the dates: txtDateInitiated and
txtDateClosed. By storing both dates you'll have duration. Since the
record wouldn't exist until it's initiated, make the default value of
txtDateInitiated =Now() displayed as Short Date. You can change the
date if necessary. For the date closed I would put some code in the
txtDateClosed textbox doubleclick event to enter today's date as
before. It it's a different date then enter it manually.

Just about every application I write has several date fields. To
facilitate date selection, I include a calendar form and the code to
run it. DoubleClicking on any date field will pop up that calendar at
the current date and allows scrolling days, months and years to choose
other dates. Selecting the chosen date puts that date into the
textbox. To jazz it up further, I also use the number pad "+" and "_"
to spin the day value up or down from a value already in the textbox.

The calendar form and the code were courtesy of Ken Getz et alia from
"The Access 2.0 Developer's Handbook: from Sybex.

HTH

 

[Jody]

Thanks. I seem to always go about things the most difficult way. Using a
"Date Initiated" in conjunction with a "Date Closed" field would be the
simplest way to go. I will need to keep the Open/Closed status box to
maintain query integrity (I don't know what all uses this field), but one
extra field to enter a date in isn't too much to ask.

Thanks again.