Please help what is the code for automatic penalty!!!

[ssanfu]

Not sure I understand what you are wanting. If you want to change the due date to Monday if the due date is a Fri, Sat or Sun, you could use the WeekDay() function to determine if the current due date is a Fri, Sat or Sun and add a number of days to get the following Monday.



The Weekday function can return any of these values:

Constant	Value	Description
vbSunday	1	Sunday
vbMonday	2	Monday
vbTuesday	3	Tuesday
vbWednesday	4	Wednesday
vbThursday	5	Thursday
vbFriday	6	Friday
vbSaturday	7	Saturday

Example:
Due Date = Sep 13 2018. (Sep 13 2018 is a Thur.)
Function WeekDay(Sep 13 2018) returns 5, therefore NOT Fri, Sat or Sun.

Due Date = Sep 14 2018. (Sep 14 2018 is a Fri.)
Function WeekDay(Sep 14 2018) returns 6, therefore IS a Fri.


If the Function WeekDay() returns a 6, 7 or 1 (Fri, Sat or Sun), then add days to Due Date
This is an example of code to change the due date:

    Dim DOW As Integer
    DOW = WeekDay(DueDate)

    Select Case DOW
          Case 1      'Sun
                 DueDate = DateAdd("d", 1, DueDate)
          Case 6      'Fri
                 DueDate = DateAdd("d", 3, DueDate)
          Case 7      'Sat
                 DueDate = DateAdd("d", 2, DueDate)
          Case Else      'all other days
                 'do nothing
End Select

[markpastoril]

thanks for the quick response sir,but where would I put this code?coz my due date was nested in a query.

[JSR]

Create a user-defined function (in a module)

public function GetNewDueDate(DueDate as date) as date

Dim DOW As Integer
    
DOW = WeekDay(DueDate)

Select Case DOW
  Case 1      'Sun
   GetNewDueDate = DateAdd("d", 1, DueDate)
  Case 6      'Fri
   GetNewDueDate = DateAdd("d", 3, DueDate)
  Case 7      'Sat
   GetNewDueDate = DateAdd("d", 2, DueDate)
  Case Else   'all other days, do nothing
   GetNewDueDate = DueDate
End Select

end function

then just refer to GetNewDueDate([DueDate]) in your query

[markpastoril]

tried each code sir, but didn't work for me.

[markpastoril]

Create a user-defined function (in a module)

public function GetNewDueDate(DueDate as date) as date

Dim DOW As Integer
    
DOW = WeekDay(DueDate)

Select Case DOW
  Case 1      'Sun
   GetNewDueDate = DateAdd("d", 1, DueDate)
  Case 6      'Fri
   GetNewDueDate = DateAdd("d", 3, DueDate)
  Case 7      'Sat
   GetNewDueDate = DateAdd("d", 2, DueDate)
  Case Else   'all other days, do nothing
   GetNewDueDate = DueDate
End Select

end function

then just refer to GetNewDueDate([DueDate]) in your query

Tried this code, and the other column did change but why are the other column did not change?pls help me through this

[pbaldy]

You aren't setting a return value for GetNewDueDate when the Else clause is met (like JSR did).

[markpastoril]

Wooah that's what i've missed sir.and it work now thanks a lot sir

[pbaldy]

No problem, JSR did the heavy lifting.

[markpastoril]

Yeah right thanks @JSR

[markpastoril]

I want to set up a senior citizens discount table sir with the details in it,and connected to customers table.how to do it properly?

[markpastoril]

Thanks @ssanfu

[markpastoril]

this it the relationship I"ve set up,is this the proper/correct way?