Caclulated Field Doesn't Always Work Properly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert T - 06 Dec 2007 18:30 GMT

I have a simple little DB that tracks my time and attendance at work. One of
the calculated fields on the form tracks the week number. I have a calculated
control with a default value of:

=Format([Date_Work],"ww")

It works properly if I'm entering the date during the current week. Today's
date is 12-06-2007 and the calculated control displays week number 49.

However, if I inadvertently miss a day and try to enter it 1 or 2 weeks
later, the calculated control displays the current week number. For example,
if I enter the date worked as 09/01/2007 today, the calculated control will
still be 49. Obviously September 1st isn't week number 49. Can someone please
explain what I'm doing wrong.

Thanks,
Robert

Reply to this Message

Linq Adams - 06 Dec 2007 18:56 GMT

The expression is valid, so my guess would be that the current value in
[Date_Work] is not what you think it is when you retroactively enter data.
How is this field populated?

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Reply to this Message

Robert T - 06 Dec 2007 21:27 GMT

Hello Linq:

Normally the Date_Worked defaults to today's date. However, when I have to
add a date retroactively, I replace today's date by typing in the old date
manually.

> The expression is valid, so my guess would be that the current value in
> [Date_Work] is not what you think it is when you retroactively enter data.
> How is this field populated?

Reply to this Message

Linq Adams - 07 Dec 2007 01:16 GMT

Is Date_Work a bound field? If so, the value in your calculated field should
change when you edit the data, as soon as you move out of the textbox that
holds Date_Work. From the behavior you've reported, it sounds like it's still
doing the calculation with the default date.

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Reply to this Message

Robert T - 07 Dec 2007 02:23 GMT

Linq:

Yes, Date_Work is a bound field in the table.

You're correct, it's still doing the calcuation with the default value which
is Date() and that's why it comes up with the wrong value if Date_Work is a
date that isn't part of the current week. I would think once the record is
saved, the calculated field should pick up the new value.

If it helps, every field has a default value and then a script that saves
the entire record.

Robert

Reply to this Message

Linq Adams - 07 Dec 2007 02:58 GMT

Actually, as I said before, once the new date is entered and you leave the
Date_Work textbox the value should be recalculated; it works like this on the
form I created to investigate this. Don't know why it's not working, but I
guess you could try a work around. They this, where YourCalculatedFieldName
is the name of your actual field, and see if it works.

Private Sub Date_Work_AfterUpdate()
Me.YourCalculatedFieldName = Format(Me.Date_Work, "ww")
End Sub

Also, if you would, post the script that you're saving the record with.

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Reply to this Message

Robert T - 07 Dec 2007 12:53 GMT

The AfterUpdate script worked in my actual field. However, a dialog box
popped up and said the DoMenu Item was cancelled. What in the world is a
DoMenu item?

Linq:

I truly appreciate your help but I owe you an apology. Your last message
made me realize that "Week" was an actual field in the table, it was NOT a
calculated control. I thought it was only a caclulated control on the form, I
completely forgot that it is a bound field from the table.

Now that I realize such, I'm guessin a calculated control would actually
work without the AfterUpdate script.

Robert

> Actually, as I said before, once the new date is entered and you leave the
> Date_Work textbox the value should be recalculated; it works like this on the
[quoted text clipped - 7 lines]
>
> Also, if you would, post the script that you're saving the record with.

Reply to this Message

Robert T - 07 Dec 2007 18:25 GMT

Linq:

Here are 2 scripts, one for the New Record button and one for Saving the
Record. All of the fields on the form are filled in by default such as the
Word_Date being today's date, the starting/ending time, etc.

Private Sub btnNewRecord_Click()
On Error GoTo Err_btnNewRecord_Click

DoCmd.GoToRecord , , acNewRec
Me!Note.SetFocus
Exit_btnNewRecord_Click:
Exit Sub

Err_btnNewRecord_Click:
MsgBox Err.Description
Resume Exit_btnNewRecord_Click

End Sub

Private Sub btnSaveRec_Click()
On Error GoTo Err_btnSaveRec_Click
Me!Week.SetFocus
Me.Dirty = True

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_btnSaveRec_Click:
Exit Sub

Err_btnSaveRec_Click:
MsgBox Err.Description
Resume Exit_btnSaveRec_Click

End Sub

Reply to this Message