Post/Update data based on DateFrom until DateUpTo in different month (Work/Leave Planning Apps)

[calaxan]

I want to make "Work Planning apps".

I have table like this:

EmpID | Month | Dy1 | Dy2 | Dy3 | Dy4 | ... | Dy31

and the record Will be like this:

EmpID  100 100
Month   3   4
Dy1     D   N
Dy2     D   N
Dy3     N   X
Dy4     N   X
. . .
. . .
Dy30    D  N
Dy31    D  N

I want to change schedule (the record) based on txt input in "txtLeaveFrom" and "txtLeaveUpTo" like this:

    myDt_min = Day(me.txtLeaveFrom)
    myMn_min = Month(me.txtLeaveFrom)
    myDt_max = Day(me.txtLeaveUpTo)
    myMn_max = Month(me.txtLeaveUpTo)

For i = myMn_min To myMn_max Step 1
      For j = myDt_min To myDt_max Step 1
         Task1 = "UPDATE tblShiftRota18_Pln SET Dy" & CStr(j) & "='" & Me.cboType & "' WHERE ((EmpID='" & Me.cboEmpID & "') AND (Mn=" & i & "))"
        
        DoCmd.SetWarnings (False)
        DoCmd.RunSQL Task1
        DoCmd.SetWarnings (True)
      Next j
    '--
Next i

The CODE works only if "txtLeaveFrom" and "txtLeaveUpTo" in the SAME MONTH, but it's not working if different MONTH.
Suppose i want to get Vacation (V) from 30 March upto 2 April, expected update data like this :

EmpID  100 100
Month   3   4
Dy1     D   V
Dy2     D   V
Dy3     N   X
Dy4     N   X
. . .
. . .
Dy30    V  N
Dy31    V  N

I hv an idea to combine with End Of the Month, but still confuse how to implemented it.

Function GetLastDayOfMonth(inputDate As Date) As Date
Dim dYear, dMonth, getDate
    dYear = Year(inputDate)
    dMonth = Month(inputDate)
    getDate = DateSerial(dYear, dMonth + 1, 0)
    GetLastDayOfMonth = getDate
End Function

How to solve this issue ?


Guide me please.

[ArviLaanemets]

Use table with different structure instead:
tblLeaves: LeaveID, EmplID, LeaveTyp, LeaveFrom, LeaveTo
and you have much less problems.

When you need for some reason data displayed in current format too, then design a query for this (not easy task though, and the query will be quite slow, and not updatable). Or design a form with recordset as source, use VBA to fill the recordset with data from table, and on save to write changes from recordset into table.

Or use Excel instead. With leave dates in a table with structure like tblLeaves, you can easily design a sheet with list of employees, where you can select a month (or year), and a table with columns for every date of selected period displays leave codes for proper days for every employee. At top of sheet you can display for every day the number of employees, the number of employees on leave, the number of employees on work, and even the total of available working hours for every day. I designed such table for departments in some factories of company - with a 3-character code for every off-day for selected year (planned or used, leave typ, workday or weekend or holyday) displayed.

[calaxan]

Thanks for the input Mr. Arvilaanemets.

But still expecting the code how to solve the problem above .

Thnks