I have an odd issue for a Access 2k DB. The project I am working on collects payments from our clients every time the client’s job pays him. So the Money we collect is applied to the month it is collected EXCEPT the last week of the month is tricky.
We consider the week to be from Sunday – Saturday. The week the money is collected the money is applied to whichever month has 4 or more days in that week. So by example, next week Saturday is the 4th of December (it has 4 or more days of the new month in it) so any payments made go to the month of December. If, however, Saturday were the 3rd any payments that week would go to November.
So is there a way programmatically to determine what month a payment applies to?
Here is my current line of thought.
I have the table Subsistance_tbl and a field paid_date.if I put in payments and dates on form [I]Frmpay[/I} and a field Date
Use Datepart (?) to determine what the date of the next Saturday from the date entered is and if it is less than 3 have some thing like Date = Date -3 and put that date in Subsistence_tbl.Paid_Date.
Seems logical but code wise I am unsure how to do it. I can figure the specifics (I hope) if anyone can nudge me in the correct direction.
I think this function will help
It works on the criteria above, takes a date value in and produces an integer Month out
Function FindMonth(ByVal Datein As Date) As String
Dim intDay As Integer
Dim satDate As Date
intDay = Weekday(Datein)
'get saturdays date
satDate = Datein + (7 - intDay)
'get the month out as an integer
Select Case Day(satDate)
Case 1 To 3
FindMonth = Month(DateSerial(Year(satDate), Month(satDate) - 1, Day(satDate)))
FindMonth = Month(satDate)