Date based on previous date

[AndyRob1973]

Hello,

I am trying to replicate something that can be done in excel (I've attached an excel spreadsheet with the example and formula in).





Basically I have a list of events that each class in a school will attend. There is an expected number of days between each event (Days from previous event). I have a formula in the 'expected date' column that takes the previous 'expected date' and adds 'Days from previous event'.....UNLESS there is an entry in the previous event's 'actual date', in which case it uses that in the calculation.

I would like to replicate this in my MS Access 2010 version of this (also attached). Could anyone help me with achieving this?

[ArviLaanemets]

Before anyone can advice you something, you have to declare all rules the calculation of predicted date must follow.

1. (Cited: "UNLESS there is an entry in the previous event's 'actual date', in which case it uses that in the calculation"). Are you saying, you have separate fields for predicted date and actual date (sorry, I haven't MS Office available currently, so I can't check it in attachments)? And when next event does happen at date different from predicted one, then next events are calculated based on last real event date?

2. Unless every "expected number of days between each event" is a multiple of seven, some predicted dates will be on weekends. What happens with those dates - are events shifted following some rules? And how are next dates after such shift predicted?

3. Same questions like in p. 2 when predicted date happens to be holiday!

4. And probably you have to leave out vacations too.

Taking all this into account, I don't see any way to do this without using some School Calendary table.

[Micron]

Haven't downloaded the files yet but I suspect it will be something along the lines of

ExpectedDate:IIF(IsNull([Actual Date]),DateAdd("d",[DFPE],[Expected Date]),DateAdd("d",[DFPE],[Actual Date]))

but that's air code and I have no idea what will happen where previous days is null.
I sure hope the actual db fields are not named like that

EDIT -OK that doesn't look right. Hang on...
corrected. Obviously I shortened that long field name just for the sake of brevity.

[Micron]

This seems to work with nulls

ExpectedDate2:IIF(IsNull([ActualDate]),DateAdd("d",Nz([DaysFromPreviousEvent],0),[ExpectedDate]),DateAdd("d",Nz([DaysFromPreviousEvent],0),[ActualDate]))

I created an additional query field rather than monkey with any existing fields. Complete sql

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;

formatted to fit code window without scrolling (there's a forced line break in there but still works as is in Access)

[Bulzie]

I think the issue I believe is he has to look back at the previous record to check the Expected Date or or Actual Date. Is that right?

[AndyRob1973]

Before anyone can advice you something, you have to declare all rules the calculation of predicted date must follow.

1. (Cited: "UNLESS there is an entry in the previous event's 'actual date', in which case it uses that in the calculation"). Are you saying, you have separate fields for predicted date and actual date (sorry, I haven't MS Office available currently, so I can't check it in attachments)? And when next event does happen at date different from predicted one, then next events are calculated based on last real event date?

2. Unless every "expected number of days between each event" is a multiple of seven, some predicted dates will be on weekends. What happens with those dates - are events shifted following some rules? And how are next dates after such shift predicted?

3. Same questions like in p. 2 when predicted date happens to be holiday!

4. And probably you have to leave out vacations too.

Taking all this into account, I don't see any way to do this without using some School Calendary table.

Thanks for your reply. Here are my answers to your questions:

1. For example, to calculate the Expected Date of Event3, if there is a date in actual date of Event2 (in the example it is 3-Jun) then the calculation is 3-Jun plus the ‘Days from previous event’ of Event 3 ie 3 June + 3 = 6 Jun

But, if there was not a date in the actual date of Event2 then the calculation would need to be 30 May + 3 = 2 June.

1. I’m not too concerned about it this as the expected date is only a guide for now.
2. As above
3. As above

I can do all this in excel but am looking for a way of replicating it in access. I attached an example database in the original post.

[AndyRob1973]

Yes that is correct Bulzie.

[AndyRob1973]

This seems to work with nulls

ExpectedDate2:IIF(IsNull([ActualDate]),DateAdd("d",Nz([DaysFromPreviousEvent],0),[ExpectedDate]),DateAdd("d",Nz([DaysFromPreviousEvent],0),[ActualDate]))

I created an additional query field rather than monkey with any existing fields. Complete sql

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;

formatted to fit code window without scrolling (there's a forced line break in there but still works as is in Access)

Thank you for your reply Micron,

The challenge I have is that I would like the calculation to use the dates from the previous event. This is something i can achieve in excel but not (so far) in access.

Using my example in the first post:

Event 2 actual date is 3-Jun
Event 3 is scheduled to happen 3 days later, so the calculation is 3-Jun + 3 = 6 Jun

However if Event 2 has no Actual Date entry (for whatever reason eg it hasn't yet occurred) then I would like to take Event 2 Expected date and add 3 days on to it ie 30 May +3 = 2 Jun.

I can do this quite easily in excel but don't know how to achieve it in access. I've attached an example access database and an excel spreadsheet (hopefully!!) illustrating it.

Hope this makes sense!

[Bulzie]

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.

[Micron]

To get a value from a prior or next record you use a subquery. See
Subqueries

[mike60smart]

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;

[Micron]

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

I guess great minds think alike? Almost what I posted in #8, letter for letter.
Seems it was not what was needed though.

[mike60smart]

Hi Micron

No my mind is nowhere as clever as yours.

I took your example and modified the Subform

[Gicu]

Here is one way of doing it with the help of a custom function.

Cheers,

[Micron]

Hi Micron

No my mind is nowhere as clever as yours.

I took your example and modified the Subform

Ahh, I see. I was hoping the OP could do that, kind of like learning to fish, ya know? Actually I thought yours was different at first because when I pasted both into Notepad to check, the FROM wrapped onto another line and I missed it. It was only after I posted and went to close the notepad text did I see the ending.