Date based on previous date

[Gicu]

And a slightly modified function to account for actual dates for subsequent events.

Cheers,


Vlad

[AndyRob1973]

Are the Event IDs static meaning there will be the same 6 (or same numbered events) for each class? I think you can do this in code on AfterUpdate on the record when you save it because if the value they entered is an ActualDate value, then you have to recalculate the remaining ExpectedDates correct? Thinking depending what changed in the record, once saved you get the values from current and previous records to do the calculations, then update the records directly in the table, then Requery the form which should now have the updated values.

Sorry for the late reply Bulzie.

For now I need to assume that the Event IDs will be static in that they will be concurrent but not necessarily starting at 1. However I wouldn't know how to construct an after event query to use the previous dates. if you could give me an example I would really appreciate it.

[AndyRob1973]

Hi Andy

In the Form that opens at startup I modified the Subforms Record Source as shown:-

SELECT Events.EventID, Events.ClassID, Events.EventName, Events.ExpectedDate, Events.ActualDate, Events.DaysFromPreviousEvent, IIf(IsNull([ActualDate]),DateAdd("d",Nz([DaysFromPreviousEvent],0),[ExpectedDate]),DateAdd("d",Nz([DaysFromPreviousEvent],0),[ActualDate])) AS ExpectedDate2 FROM Events;

Thankyou Mike,

I might not have explained myself fully (sorry!) but the ExpectedDate of say Event 3 is determined by either the ExpectedDate or ActualDate of Event 2.

If ActualDate in Event 2 is null then the ExpectedDate of Event 3 is - ExpectedDate of Event 2 PLUS Days from previous event.

However if ActualDate in Event 2 has a avalue then this is used to calculate ExpectedDate of Event 3 ie - ActualDate of Event 2 PLUS Days from previous event.

I hope this makes sense!

Thankyou!

[Gicu]

Have you looked at the latest sample from post #16?
Cheers,

[AndyRob1973]

And a slightly modified function to account for actual dates for subsequent events.

Cheers,
Vlad

Sorry for the late response vlad.

This is superb - thankyou! There is just one missing piece.

It throws an error 'invalid use of null' when i try to enter a date in a different class.

I hope this makes some sense and thankyou!


Andy

[Gicu]

Can you please upload a screen shot with the data that throws the error (or even better just upload the modified file)?
Cheers,
Vlad

[AndyRob1973]

Hi Gicu,

Yes i saw the post 16 and it was really helpful. I think you've pretty much done what I'm trying achieve. The error i was getting is below, but i think I've realised that it is because i was entering a date before i had entered an 'EventName' or 'DaysFromPreviousEvent'. It seems to work now.

However, I've noticed that when you click in the Calc_Expect field the date changes temporarily until you click out.

Also if you were able, could you help me to learn by explaining what your module code does? I know a bit about vba but an explanation would really help my learning - especially how you access the data in a previous row.

Thankyou!

[Gicu]

Here is the module:

Option Compare Database
Option Explicit
Public datPreviousDate As Date
Public Function vcExpectedEvent(iEventID As Integer) As Date
Dim rs As DAO.Recordset, datEventDate As Date, iFirst As Integer
'Exit function


Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 Events.* FROM Events WHERE EventID <" & iEventID & " Order By EventID DESC;")


'first event
iFirst = DMin("EventID", "Events")
If iEventID = iFirst Then
    datPreviousDate = #12:00:00 AM# 'reset public variable
    vcExpectedEvent = DLookup("EventDate", "qryEvents", "EventID=" & iEventID)
    GoTo Exit_vcExpectedEvent
End If
If datPreviousDate <> #12:00:00 AM# Then
    datEventDate = IIf(IsNull(rs!ActualDate), datPreviousDate, rs!ActualDate)
    vcExpectedEvent = DateAdd("d", DLookup("DaysFromPreviousEvent", "Events", "EventID=" & iEventID), datEventDate)
Else
    datEventDate = IIf(IsNull(rs!ActualDate), rs!ExpectedDate, rs!ActualDate)
    vcExpectedEvent = DateAdd("d", DLookup("DaysFromPreviousEvent", "Events", "EventID=" & iEventID), datEventDate)
End If
Exit_vcExpectedEvent:
rs.Close
Set rs = Nothing
datPreviousDate = vcExpectedEvent 'preserve for next run
End Function

It uses a public variable (datPreviousDate) to preserve the previous event date for each event as it loops through; it also uses a second query (qryEvents) which simply chooses the date:EventDate: IIf(IsNull([ActualDate]),[ExpectedDate],[ActualDate]).
Have a look at it, should be fairly easy to follow but please do post back if you want more clarifications.

Cheers,
Vlad

[AndyRob1973]

Here is the module:

Option Compare Database
Option Explicit
Public datPreviousDate As Date
Public Function vcExpectedEvent(iEventID As Integer) As Date
Dim rs As DAO.Recordset, datEventDate As Date, iFirst As Integer
'Exit function


Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 Events.* FROM Events WHERE EventID <" & iEventID & " Order By EventID DESC;")


'first event
iFirst = DMin("EventID", "Events")
If iEventID = iFirst Then
    datPreviousDate = #12:00:00 AM# 'reset public variable
    vcExpectedEvent = DLookup("EventDate", "qryEvents", "EventID=" & iEventID)
    GoTo Exit_vcExpectedEvent
End If
If datPreviousDate <> #12:00:00 AM# Then
    datEventDate = IIf(IsNull(rs!ActualDate), datPreviousDate, rs!ActualDate)
    vcExpectedEvent = DateAdd("d", DLookup("DaysFromPreviousEvent", "Events", "EventID=" & iEventID), datEventDate)
Else
    datEventDate = IIf(IsNull(rs!ActualDate), rs!ExpectedDate, rs!ActualDate)
    vcExpectedEvent = DateAdd("d", DLookup("DaysFromPreviousEvent", "Events", "EventID=" & iEventID), datEventDate)
End If
Exit_vcExpectedEvent:
rs.Close
Set rs = Nothing
datPreviousDate = vcExpectedEvent 'preserve for next run
End Function

It uses a public variable (datPreviousDate) to preserve the previous event date for each event as it loops through; it also uses a second query (qryEvents) which simply chooses the date:EventDate: IIf(IsNull([ActualDate]),[ExpectedDate],[ActualDate]).
Have a look at it, should be fairly easy to follow but please do post back if you want more clarifications.

Cheers,
Vlad

Cheers Vlad. I think i need to start by getting to grips with DAO.Recordset as I'm unsure what it is at moment.

[AndyRob1973]

Here is the module:

Option Compare Database
Option Explicit
Public datPreviousDate As Date
Public Function vcExpectedEvent(iEventID As Integer) As Date
Dim rs As DAO.Recordset, datEventDate As Date, iFirst As Integer
'Exit function


Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 Events.* FROM Events WHERE EventID <" & iEventID & " Order By EventID DESC;")


'first event
iFirst = DMin("EventID", "Events")
If iEventID = iFirst Then
    datPreviousDate = #12:00:00 AM# 'reset public variable
    vcExpectedEvent = DLookup("EventDate", "qryEvents", "EventID=" & iEventID)
    GoTo Exit_vcExpectedEvent
End If
If datPreviousDate <> #12:00:00 AM# Then
    datEventDate = IIf(IsNull(rs!ActualDate), datPreviousDate, rs!ActualDate)
    vcExpectedEvent = DateAdd("d", DLookup("DaysFromPreviousEvent", "Events", "EventID=" & iEventID), datEventDate)
Else
    datEventDate = IIf(IsNull(rs!ActualDate), rs!ExpectedDate, rs!ActualDate)
    vcExpectedEvent = DateAdd("d", DLookup("DaysFromPreviousEvent", "Events", "EventID=" & iEventID), datEventDate)
End If
Exit_vcExpectedEvent:
rs.Close
Set rs = Nothing
datPreviousDate = vcExpectedEvent 'preserve for next run
End Function

It uses a public variable (datPreviousDate) to preserve the previous event date for each event as it loops through; it also uses a second query (qryEvents) which simply chooses the date:EventDate: IIf(IsNull([ActualDate]),[ExpectedDate],[ActualDate]).
Have a look at it, should be fairly easy to follow but please do post back if you want more clarifications.

Cheers,
Vlad

Thanks Vlad,

I think i understand most of it but can't work out how the code 'loops' through the rows. I was expecting a loop command somewhere. Could you help?

Thanks

Andy

[Gicu]

Hi Andy,

The looping happens in the query, the function gets called individually once for every row.

Cheers,
Vlad

[AndyRob1973]

Thank you Gicu,