Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Tampa, FL

    Unanswered: Semi-confusing issue

    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.


  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland
    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
    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