Difference between dates

[medof]

Regards

I have the attached database which calculate the Difference between dates .
I have also two problems like i shown in the image

MarkK tried to help me before with the code

Option Compare Database
Option Explicit

Sub Recalculate()
    Dim rst As DAO.Recordset
    Dim dff As Long
    Dim nxt As Long
    Dim ttl As Long
    Dim Name As String
    Dim tot As Long
    
    Set rst = Me.RecordsetClone
    
    With CurrentDb.OpenRecordset( _
        "SELECT * FROM Table2 ORDER BY Name, Value" _
    )
        .MoveFirst
        Do While Not .EOF
            .Edit
            If Name <> !Name Then
                Name = !Name
                ttl = !Value
                !Next = Null
                !Diff = Null
                !Total = Null
            Else
                !Next = nxt
                !Diff = !Value - nxt
                ttl = ttl + !Value
                !Total = ttl
            End If
            .Update
            nxt = !Value
            .MoveNext
        Loop
        .Close
    End With
    Me.Recordset.Requery
    
End Sub

Private Sub Form_Load()
    Recalculate
End Sub

But i can't do it with dates .. and in my database
Thanks ...

 

[plog]

I think you need to take a few step backs and tells us what you are actually trying to do. No database jargon, just in plain english what this system is suppose to represent.

The first thing I see is that your form isn't based on a table but an unupdateable query. Forms should be based on tables, when you adhere to that, you can always add/edit data to them. If you need to display calculated values in each row on a form, then you do the calculation and display it in an unbound control. When you do that, you can control what displays--you'd check your result to see if its negative and then display what you want to show instead of the negative.

Again though, that's just the big picture isue with your database. I don't know what it does, I just see what you did wrong. To do it correctly, I'd need to know what you are trying to do--again, without using database/technical jargon. Explain to a 6th grader what this system represents.

 

[medof]

Thanks plog for your time

I'm trying to organize my small company by make a database to know when the Employee take a vacation .... its period ... the coming date .... and the period since the last vacation

Start_Vac the first day of vacation
End_Vac the last day of vacation
Vac the period
WorkDays the count of days between today and the last day of vacation to count workdays

sorry ... i just started to learn Access

Thanks again

 

[plog]

Good explanation. Your current implementation is overkill and my initial assesment was correct.

To accomplish what you have described (I'm overlooking your other tables in that database you posted--I'm sure they are relevant to other things, but not to this), all you need is a form based off of Vacations. It would have bound inputs for Start_Vac, End_Vac (not sure what Vac field is). Bound inputs mean you have a control and you use a field from the table in the Control Source ([Start_VAC] & [End_Vac]). You would have a 3rd control and its control source would look like this:

=get_WorkDays([Start_Vac], [End_Vac])

That's how you would use your function. You would pass it the two dates it needs and it would return (and display in that control) the value it calculates. In that function you can test it to see if it was negative before returning and then return a default value (0, or N/A or null).

Right now you have a Recalculate function, but I don't really understand it and its overkill. No need to open a recordset and loop through it. What you will need is a calculation to determine the WorkDays. I'm sure that's been accomplished on this forum--search it for the code you need.

 

[medof]

Thanks for trying help me ... i'll try again