Field to display last Friday's date by default

[lsmcal1984]

Hi,

I have a field on my form that needs to display last Friday's date by default.

This is on an unbound form which is used to build a query displaying hours between two dates.

Many thanks!

[Dal Jeanis]

Put this in the Form_Load event (or in your autoexec macro).

Private Sub Form_Load()
    TempVars.Add "DefaultFriday", DateAdd("d", IIf(Weekday(Date, [vbFriday]) > 0, -Weekday(Date, [vbFriday]), -7), Date)
End Sub

Put this in the field's default value property

=[TempVars].[Item]("DefaultFriday")

[lsmcal1984]

Thank you for your quick response
Right now it is displaying last Thursday's date rather that last Friday... any ideas? I tried amending the -7 to -6 but it didn't change it.
Otherwise it works great!
Leo

[Missinglinq]

Add +1 to the end of either line of code:

=[TempVars].[Item]("DefaultFriday") +1

or

TempVars.Add "DefaultFriday", DateAdd("d", IIf(Weekday(Date, [vbFriday]) > 0, -Weekday(Date, [vbFriday]), -7), Date) + 1

Personally, I'd use the second example. Don't know if it matters, here, but also note that if you run this code on a Friday it will give you the current date

Linq ;0)>

[lsmcal1984]

Excellent, works great. Thanks!

[Missinglinq]

Glad we could help!

Welcome to the Forum!

Linq ;0)>

[Dal Jeanis]

That was what the -7 was for, but I was assuming Weekday returned 0 thru 6 rather than 1 thru 7. This is the correct (and simpler) code -

TempVars.Add "DefaultFriday", DateAdd("d", -Weekday(Date, [vbSaturday]), Date)

[Missinglinq]

Elegant! And that resolves the problem (from Post #4) of generating the date on a Friday and getting the current Friday!

It would be nice if the Access Gnomes either made everything Zero-based or nothing Zero-based!

Linq ;0)>

[Dal Jeanis]

Naw, the Access gnomes had nothing to do with me misreading http://www.techonthenet.com/access/f...te/weekday.php.

I don't even have the excuse that it was aircode... I tested the code I posted, but didn't bother to check to see that the result was a Friday!

[Missinglinq]

Even Allen Browne makes mistakes, though not many!

Linq ;0)>

[Dal Jeanis]

Hey, if I can't laugh at myself, I'll miss nearly half the available jokes...

[Missinglinq]

And why should you miss all the fun?