Code and where to put it

[bastraker via AccessMonster.com]

Hello all

I'm really making some strides with this database, thanx to long hours and
everyone on this board, many thanks.

On to the new question. I have a table in my database, each record has a
"commited completion date" field and "On time" field. The requesting
individual would like to be notified through report, which they will generate,
if something or someone misses their target. If there is no completion date
yet, Null, I want the code to look at the current date. I think it will be
easy enough with an if statement and using Date(), I hope anyway.

My question is where can I put the code so that it will run on every record
and update the on time field with the proper string prior to the report being
generated? I don't want the user to have to cycle through all the records in
a form first.

Thanks in advance for any assistance
D

 

[John Spencer]

Instead of actually updating a field, use a calculated field and the NZ
function.

Something like
Field: CalcDoneDate: NZ([Committed Completion Date],Date())

If Committed Completion Date is Null then the field will show the current
date. You can apply your criteria against that calculated date field.

 

[bastraker via AccessMonster.com]

Thanx for the input John

But perhaps I was not clear in my description so I will simplify the question.
Is there anywhere I can put the code such that it will run on every record in
a table without the user having to cycle through them in a form.

Besides that I will elaborate in case anyone is interested or needs more on
the situation. Each record is made up a a few different fields with the major
players in this case being "Commited Completion Date", "Actual Completion
Date", and "On Time".

The "Commited Completion Date" will never be "Null", the "Actual Completion
Date" will be until the task it represents is complete. Neither of those
fields should be updated by the code, only "On Time" with the value of Yes or
Missed Target.

As it stands right now I have code determining what to set the value of "On
Time" as (Missed Target or Yes) in a Before Update Event by comparing user
inputed "Commited Completion Date" and "Actual Completion Date" fields.
However in some circumstances individuals may already be way past the
"Commited Completion Date" before they have completed the task and get a date
entry made in the "Actual Completion Date" field. Therefore I would like to
be able to run some code to look at all "Null" "Actual Completion Date"
fields and determine if they have missed their target date prior to a weekely
report being generated.

I don't want the user to have to cycle through the records in a form for the
code to run on each, but I'm not sure if this is possible.

Once again any assitance would be greatly apprecited
D

 

[John Spencer]

So what you are trying to do is identify records where Actual Completion
date is after Committed Completion date and if Actual completion date is
Null then use today's date for the comparison. Is that correct?

If so, you can do that in a query and not update "On Time" at all. In
theory, On Time should not be a field in your database at all since you can
calculate it from other fields in the table. That is the value of On Time
is dependent on other fields in the table.

This expression should calculate On Time for you in a query.

IIF(Nz([Actual Completion Date],Date()) > [Committed Completion
Date],"Missed Target","Yes")

If you feel that you really need to update the On Time field you can use
that expression in an update query.

UPDATE [Your Table Name]
SET [On Time] = IIF(Nz([Actual Completion Date],Date()) > [Committed
Completion Date],"Missed Target","Yes")

If you do decide to run the update query, TEST it on a COPY of your database
to see if it functions the way you want. There is no undo on update
queries.

 

[bastraker via AccessMonster.com]

Hey John

That expression in an update query worked like a charm. Many thanx bud!!!

The "On Time" value needs to be held in the table for other reports that are
generated and form views the user uses. I guess I could have the database run
the expression prior to every time the user wants to access that piece of
data, but the update query works great for now.

Thanx again
D