Assistance needed in assigning a variable

[Voyager]

Hi Experts,
I am using the below code to update the "freeat" time but I am not getting the right calculation. Could you assist? Find the screenshot

1) In the first row "freeat" time for emp4 is "01/11/19 3:10" when he picks the second task ( second row ) he will be free at "01/11/19 15:10" since "tasktime" is 12 hrs.
(01/11/19 3:10 + 12:00 gives 01/11/19 15:10)

Till this code is fine but from the third row freeat time is not correct ("01/11/19 14:10" is the result as it still incorrectly takes freeat as (01/11/19 3:10 + 11:00 ) instead of
(01/11/19 15:10 +11:00) and so on

I understand I have done a mistake here "

 arrfree(UBound(arrfree)) = ![freeat]"

but how do I assign a variable

Private Sub upd()
    Dim rsTask As DAO.Recordset
    Dim intCount As Integer
    Dim intNextCount As Integer
    Dim arrEmp() As String
    Dim arrAvail() As Date
    Dim arrfree() As Date
    
    ReDim arrEmp(0)
    ReDim arrAvail(0)
    ReDim arrfree(0)
    Set rsTask = CurrentDb.OpenRecordset("select * from tasks order by taskpick;")

    With rsTask
        .MoveFirst
        intNextCount = 1
        While Not .EOF
            If (!employee & "") <> "" And IsNull(![avlto]) = False Then
                ReDim Preserve arrEmp(UBound(arrEmp) + 1)
                ReDim Preserve arrAvail(UBound(arrAvail) + 1)
                ReDim Preserve arrfree(UBound(arrfree) + 1)
                
                arrEmp(UBound(arrEmp)) = !employee
                arrAvail(UBound(arrAvail)) = ![avlto]
                arrfree(UBound(arrfree)) = ![freeat]
            Else
                For intCount = intNextCount To UBound(arrEmp)
                    If arrAvail(intCount) > !taskpick Then
                        .Edit
                        !employee = arrEmp(intCount)
                        ![avlto] = arrAvail(intCount)
                        ![freeat] = arrfree(intCount) + ![tasktime]
                        .Update
                        intNextCount = intNextCount + 1
                        If intNextCount > UBound(arrEmp) Then intNextCount = 1
                        Exit For
                    End If
                Next
            End If
            .MoveNext
        Wend
    End With

 

[isladogs]

Just wondering whether you need any of that code. How about using an expression

Freeat = CDate(CDbl(Taskpick)+CDbl([Task Time]))

 

[Voyager]

Thanks for immediate response.
I tried using it but getting error as Microsoft access cant find the field referred in your expression. I adjusted the space between taskt time. Assigned it as a variant and choose in the ![freeat] field. added a square bracket to taskpick Still getting that

![freeat] = CDate(CDbl([Taskpick]) + CDbl([TaskTime]))

 

[isladogs]

You shouldn't be assigning as variant ... or as anything else

I meant to scrap the entire recordset code and use that as an expression in a query. The 'freeat' field is unnecessary as it is calculated data.

If you must use a recordset, you need to include the ! for each of the field names. But doing so will be slower as it will run row by row.

 

[Voyager]

Hi,
I agree with your suggestion.
But now I tried running the code but its not giving the desired results. For emp4 starting from second row freeat shows the same value. I have given the screenshot.

I think I should be more clear.Though it is a calculated field. It has to calculate freeat time of previous field for the same employee with the tasktime of the next field for the same employee. Is that possible?

 

[isladogs]

Yes its possible though obviously more complicated
Not sure which is the previous record for the same employee.
Is that by date or taskid?

It would be easier if the taskid & empid field were numbers e.g. 21 instead of task21 as you could more easily each field sort in number order. If you look at the test sort order you have for taskid, you'll see what I mean

Suggest you look at the examples in this article on my website: Get Value in Previous Record

 

[Voyager]

Hi Isladogs,
Thank you. I am looking at the DB downloaded from your site, Looks great. I need to work how to get that concept into this DB.

Meantime I am sharing my DB for your reference it would be of great help if you can provide a way forward.
You can click the update and clear buttons to get an overview and see the incorrect "freeat" time

The given code sorts the data based on "taskpick"
Then chooses employee and check for this availability time and if it is lesser than taskpick it assigns that employee and his freetime.
This freetime will act as pickuptime for his next task and by adding the corresponding records tasktime we can get the new free time.

My issue is how to use the freetime of his previous task and sumup with tasktime of new record?

 

[Cronk]

Before Isladogs comes back with more specific information, you could tidy up your table design.


As already suggested, use numeric ID fields to link your table. Rename the ID in Emp table to EmployeeID, and that in the task table to TaskID. By all means, use EmpName and TaskName to give a meaningful moniker for display purposes.


Then include an EmpID field in your Task table and link on that field.


On the matter of links, your query clr has no link between the two tables and as the field AvlTo is in both tables in the query, you need to specify which one is to be used in the query eg Emp.AvlTo

 

[isladogs]

I've had a quick look but I'm unclear how tasks are assigned to each person or why the task order in the left subform (queform) is in the order shown.

It would help considerably if you provided a screenshot or excel file with the values you actually want to get using code

It would also definitely help to follow up the points I made & Cronk reiterated

I can't promise I'll have time to look at this properly in the next day or two.
Hopefully someone else will do so rather than you be kept waiting for too long.