Enter today's date automatically on Change (1 Viewer)

[randolphoralph]

I am still learning Access and need some help.

My table has a field named Today and Number.

I need the Today field to automatically enter the current date when the Number field is changed.

I believe I need to use =Date() to accomplish this but not sure where to put it.

Any help would be greatly appreciated.

 

[boblarson]

Defaults at table level will ONLY work when there is a new record. You would have to code it into your form's Before Update event.

 

[randolphoralph]

Here is what I have done to get it to work.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.Today & "") = 0 Then
Me.Today = Date
End If
End Sub

How can I change the code above to get it to display yesterday's date when the current date is a Tuesday-Friday and Friday's date when the current date is Monday?

 

[randolphoralph]

So in other words I am looking for the workday (assuming the workweek is Monday through Friday)

 

[missinglinq]

"I need the Today field to automatically enter the current date when the Number field is changed."

First off, if the object is to only change the Today field when the Number field is changed, then the Number_BeforeUpdate event is where the code should be placed, not in the Form_BeforeUpdate event. The Form_BeforeUpdate event would be the correct choice if you wanted the date changed when any field in the record was changed, but not when a change is made to only a certain field.

Next off, this code

If Len(Me.Today & "") = 0 Then
 Me.Today = Date
End If

does not check to see if the value for the Number has been changed. It checks to see if the field Today has a value. It could have a value, from a previous time when Number was changed, and this code would leave it as it was, even if the Number field was changed again. In fact, the field could be changed a dozen times and the date in Today would be the date data was first entered in the Number field.

This code

Private Sub Number_BeforeUpdate(Cancel As Integer)
 If Weekday(Date) > 2 And Weekday(Date) < 7 Then
  Me.Today = Date - 1
 ElseIf Weekday(Date) = 2 Then
  Me.Today = Date - 3
 End If
End Sub

will do as you've requested. If Number is changed, Today will be updated to the date of the previous Friday if the day of actual change is on a Monday, and to the previous day's date if the day of change is on a Tuesday thru Friday.

Lastly, Number is a Reserved Word in Access VBA and shouldn't be used as the name of a field or control. I've left it intact in the code above, but you really should change it. Not doing so could lead to problems later on.

 

[randolphoralph]

missinglinq,

Wow thank you. I did not know that about the field name Number so that is good to know. I will rename it to a different name.

One last question....How would I get the code below to account for any holidays that fall on a weekday? So lets say for example that it is Monday and Friday was a holiday then I would need to have it enter Thursday's date.

Private Sub Number_BeforeUpdate(Cancel As Integer)
If Weekday(Date) > 2 And Weekday(Date) < 7 Then
  Me.Today = Date - 1
ElseIf Weekday(Date) = 2 Then
  Me.Today = Date - 3
End If
End Sub

 

[DCrake]

The question that interests me is why you are changing the date to one earlier than the actual date the number was changed.

Surely you want to know the actual date it changed not the previous working date.

 

[randolphoralph]

I know this is a unusual request. The reason the date is being changed to the previous work date is that the database is being used to check the work completed on the previous work day.

 

[gemma-the-husky]

imo, you would still be better to store the actual date the thing happened. you can always take holidays into account at a later stage, when presenting the data.

 

[randolphoralph]

I agree and updating them after they are stored would be my preference, but since the dates are being used by the users of the form they have to reflect the correct date on the fly.

 

[DCrake]

So Henry, who is a nice chappy, but not a very reliable timekeeper, was using the computer last Tuesday to update his records. He changed the Number field on a particular record just before he went home for the evening.

Now according to your logic when he entered the new number the trigger changed the date from last Tuesday to the previous Friday. Oh I forgot to tell you that last Monday was a bank holiday and there was nobody working that day so it could not have been then.

Anyway the impact of Henry changing the number meant that something serious happened and a vital piece of work was overlooked, and the boss is furious. "Who is responsible for this cockup" he asks. So you go to the computer and look at the date in the table when the number was changed. And rightly so it says the Friday date (as you told it to do) but now you need to know who actually changed it. Your audit trail says it was poor old Henry, "Hey but hang on says Henry. I was not in that Friday, I was sick, so how could it have been me?".

In the end Henry kept his job but the database was fired.

The moral of this story is why keep audit trails if the information that they contain is inaccruate.

 

[missinglinq]

To be honest, this is rapidly becoming a mare's nest! Basically, you'd have to

1. Create a HolidayTable to hold dates for all holidays for the current year
2. After calculating your Today date, you'd check it against this table, using DCount()
3. If DCount() returns a value greater than zero, you'd have to check to see if this date was a Monday or a Friday (as the original code does)
4. After determining the day, adjust Today accordingly.

And, of course, this doesn't take into account holidays that don't always fall on a Monday or a Friday, like Christmas and New Year's Day.

You might want to rethink this! If it was me, I'd use the original code I posted and simply manually correct the Today to take into account holidays. In my experience, employees are usually keenly aware of holidays!

Good luck!

 

[randolphoralph]

Missingling,

Thanks for your help. I think I will take some time and see if I can rethink this.

 

[DCrake]

If you do decide to have a table that contains a list of known holidays then I would also suggest you also have a second field that becomes the alternative date. For example, lets take Good Friday and Easter Monday. A person changes the number on a Tuesday, so according to logic it then should retreat one working day. So in theory it should be Monday. But Monday is a bank holiday. So we say retreat to Friday, but again this is also a bank holiday.

But if we had a date in the table for the Monday as a holiday date and the previous Thursday as the alternative date then all we need to do is to do a lookup on the alternative date where the holiday date = the revised date.

I submitted a sample database that contains about 25yrs of dates and alot of different fields that indicated what day of the week and it was, was it a holiday, was it a week end date, etc. Take a look at this this may be a good reference point for you.
See this link.

 

[randolphoralph]

DCrake,

I think your idea will provide the solution that I am looking for. Here is what I have.

I am not sure how to do the lookup on the alternative date where the holiday date = the revised date as you stated. I have bolded my failed attempt in the code below.

Any thoughts?

Private Sub Number_BeforeUpdate(Cancel As Integer)
If Len(Me.Date_of_Work & "") = 0 And Weekday(Date) > 2 And Weekday(Date) < 7 Then
  Me.Date_of_Work = Date - 1
ElseIf Weekday(Date) = 2 Then
  Me.Date_of_Work = Date - 3
End If
If DCount("*", "Holiday", "Holiday = #" & Forms![Audit]![Date of Work] & "#") > 0 Then
[B]Me.Date_of_Work = Tables![Holiday]![AlternativeDate][/B]
End If
End Sub

 

[randolphoralph]

Well after trying different things I found out what I needed. Here is the solution.

Private Sub Number_BeforeUpdate(Cancel As Integer)
If Len(Me.Date_of_Work & "") = 0 And Weekday(Date) > 2 And Weekday(Date) < 7 Then
  Me.Date_of_Work = Date - 1
ElseIf Weekday(Date) = 2 Then
  Me.Date_of_Work = Date - 3
End If
If DCount("*", "Holiday", "Holiday = #" & Forms![Audit]![Date of Work] & "#") > 0 Then
Me.Date_of_Work = DLookup("AlternativeDate", "Holiday", "Holiday = #" & Forms![HCF Audit]![Date of Work] & "#")
End If
End Sub

I want to say Thank you to everyone that helped on this. Sorry for being such a pain.

 

[DCrake]

There is still room for improvement here. As your final IF is performing both a DCount() and a DLookup().

Dim a temp date variable and do the following

Dim TmpDate AS Date
tmpdate = Nz(DLookup("AlternativeDate", "Holiday", "Holiday = #" & Forms![HCF Audit]![Date of Work] & "#"),0)

If TmpDate <> 0 Then
   Me.Date_of_Work = TmpDate
End If