Week-Ending Date

[brett429]

When using a form to create a new record, I want a field for the week-ending date to automatically fill. Right now the user has to type the date in manually. We sometimes sort our records by week-ending date (each Friday) to know which records were created for each week. For example, all records created this week, users are typing in "04/11/2008" in the week-ending field.


However, if an existing record is edited, I wouldn't want the week-ending field to update to the current Friday - it should remain as it originally was when created.

I hope this makes sense. Any way to do this? THANKS!

 

[brett429]

>>>We sometimes sort our records by week-ending date<<<

You may be better off calculating the week ending date when you do this sorting.

in any event whether you calculate this, or whether you store it, you probably need to have a "record created date" stored in your record, and use this to produce the week ending date.

That way when you make future updates to the record, the calculation will be based on the created date but not the current date, avoiding the problem I believe you are experienced in that the week ending date changes when you update a record. (I'm guessing this from the content of your post)

You make a good point. I actually haven't implemented anything at this point, I was just trying to think ahead of possible problems. How would I go about creating a "record creation" date? I'd want this to be hidden and behind the scenes, of course. And from there, how would the week-ending entry work? This can be visible to the user.

Thanks!

 

[DanG]

Maximum Form Height Reached

I have a very long form which is not for data entry, it is for management to review only. I would really like to have it run continuously downward but I seemed to have reached the maximum height of 22".

I would like to not have to use tabs as suggested in my researching the problem (as this running section is already one of three tabs itself). Is there another way to get around this?

Thank you

 

[RuralGuy]

Set the Default value of that control to: = Date + (5 - Weekday(Date, vbMonday))

 

[Brianwarnock]

If they can work on a Saturday he will need to check for that and add 6 to the date.

Brian

 

[Brianwarnock]

Dan you need to delete your post from this thread and start a new one.

Brian

 

[RuralGuy]

If they can work on a Saturday he will need to check for that and add 6 to the date.

Brian

In that case: = Date + (7 - Weekday(Date, vbSaturday))

 

[brett429]

Set the Default value of that control to: = Date + (5 - Weekday(Date, vbMonday))

I copied exactly what you wrote into the "default" field using the expression builder, but it displays as "#Error" on the form. What did I do wrong?

 

[RuralGuy]

Hmmm... We may have to put this in the Current Event inside a If Me.NewRecord test. Did Brian have it right in that if they are working on Saturday it should be the next Friday's date?

 

[brett429]

Hmmm... We may have to put this in the Current Event inside a If Me.NewRecord test. Did Brian have it right in that if they are working on Saturday it should be the next Friday's date?

No one would be working on Friday, but if that were the case, you'd be correct. Friday would be the last day of the work week, so that would be the last date we'd use the current "week ending" for. Can you elaborate on your first sentence? I'm not incredibly Access-savvy, but I can navigate around if you explain it. Thanks again!

 

[RuralGuy]

Something like this in the Current Event of your form:

Private Sub Form_Current()

   If Me.NewRecord Then
      Me.YourDateControlName = Date + (7 - Weekday(Date, vbSaturday))
   End If
   
End Sub

...using YOUR DATE CONTROL NAME of course.

 

[brett429]

Something like this in the Current Event of your form:

Private Sub Form_Current()

   If Me.NewRecord Then
      Me.YourDateControlName = Date + (7 - Weekday(Date, vbSaturday))
   End If
   
End Sub

...using YOUR DATE CONTROL NAME of course.

Perfect! Thank you so much!

 

[Brianwarnock]

In that case: = Date + (7 - Weekday(Date, vbSaturday))

That's neat! I forget about this vbDay stuff and was thinking interms of If .

Brian

 

[RuralGuy]

Glad to be of help.