1. Registered User
Join Date
Sep 2004
Location
Tampa, FL
Posts
520

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.

Thanks!

2. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067
Morning Darasen

I think this function will help
It works on the criteria above, takes a date value in and produces an integer Month out
Code:
```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)))
Case Else
FindMonth = Month(satDate)
End Select

End Function```

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•