Find date for certain day of month

[bazzanoid]

Afternoon all,

I'm manipulating dates and days quite a bit at the moment, and I need to identify the last thursday of whichever month we are currently in - we bill our account customers on this day so ongoing invoices need to be raised with this date in mind. I know I can select manually using a date picker, but going forward i'm trying to automate the closing and opening of some invoices so need to be able to do this automatically. Any help would be appreciated!

I've already got procedure in place for 7,14 or 28 days from date chosen using this code, if it helps give some idea of the context of use:

Me.DateDue = DateAdd("y", 14, [DateOut])

TIA

Barry

 

[plog]

You can do this, its a good beginner project for VBA. Here's a list of VBA functions that I will reference. To begin, you first first think about it your issue logically, sketch out some logical statements, then convert it to VBA.

The last thursday of the month occurs at least on the 22nd of the month. So make a date variable with that value (you will need to use Month(), Year() and CDate()). Then you create a loop to test every day from the 22nd to the end of the month to see if its a Thursday (will need to use Weekday()). As you go through that loop, if you find a day that is thursday you set it to be the value you return. If you encounter 2 thursdays the loop will set the last one found to be the one returned.

Give it a shot and post back here any issues you have (or more than likely on this forum, wait 30 minutes and someone will give you the solution).

 

[bazzanoid]

I'll give it a crack, then see what i've done wrong when someone else posts the solution

I can quite happily play with forms, reports, etc but VBA is something i've not touched on in any real detail in many years. Now i'm getting back into it, it mostly makes sense and I'll be going proper deep with some of the stuff i need to accomplish on this database!

 

[bazzanoid]

Right. Sussed it by using

Me.DateDue = DateSerial(Year([DateOut]), Month([DateOut]) + 1, 1) - Weekday(DateSerial(Year([DateOut]), Month([DateOut]) + 1, 1), vbFriday)

However, the problem I now face is if the DateOut is after the last thursday of the month but still within that month, it still returns that month's thursday. How can I change this to apply next month's last thursday in these cases?

 

[arnelgp]

then create a function:

Public Function fnLastThursOfMonth(Optional D as date=0) as date
Dim D1 As Date
If D = 0 then D = Date()
D1 = DateSerial(Year(D), Month(D) + 1, 0)
While WeekdayName(Weekday(D1)) <> "Thursday"
D1 = D1 - 1
Wend
If D1 <= D then
D1 = DateSerial(Year(D), Month(D) + 2, 0)
While WeekdayName(Weekday(D1)) <> "Thursday"
D1 = D1 - 1
Wend
End If
'Debug.Print WeekdayName(Weekday(D))
fnLastThursOfMonth=D1
End Function

 

[TJPoorman]

Or you could use your formula you have with an if statement. If the date generated is less than what you put in then use 2 months out:

Me.DateDue = DateSerial(Year([DateOut]), Month([DateOut]) + 1, 1) - Weekday(DateSerial(Year([DateOut]), Month([DateOut]) + 1, 1), vbFriday)

If Me.DateDue < [DateOut] Then
    Me.DateDue = DateSerial(Year([DateOut]), Month([DateOut]) + 2, 1) - Weekday(DateSerial(Year([DateOut]), Month([DateOut]) + 2, 1), vbFriday)
End If

 

[bazzanoid]

Awesomeness thanks guys

Every little issue solved is a step toward completion..... and then finding out another two hundred features that need to be added!