Sub Query for datediff

Question

Hi i am trying to create a sub query for the function of DateDiff so i can use that data to calculate recurring dates. However i am strugling to create the sub query, could anyone help?

I have the function Datediff as DateDiff([PeriodTypeID],[EventStart],[EventEnd]) but i am strugling to write a working subquery.

Thanks

Answer 1

Or are you saying you want the dates of events at regular intervals over a range defined by start and end dates?  Again this would require a loop, e.g. to return the dates of 3-day events every two weeks over the next year:

    Const StartDate = #7/2/2018#

    Const EndDate = #7/2/2019#

    Dim dtmDate As Date

    dtmDate = StartDate

    Do Until dtmDate >= EndDate

        Debug.Print dtmDate, DateAdd("d", 2, dtmDate)

        dtmDate = DateAdd("d", 14, dtmDate)

    Loop

which returns:

02/07/2018    04/07/2018

16/07/2018    18/07/2018

30/07/2018    01/08/2018

13/08/2018    15/08/2018

27/08/2018    29/08/2018

10/09/2018    12/09/2018

24/09/2018    26/09/2018

08/10/2018    10/10/2018

22/10/2018    24/10/2018

05/11/2018    07/11/2018

19/11/2018    21/11/2018

03/12/2018    05/12/2018

17/12/2018    19/12/2018

31/12/2018    02/01/2019

14/01/2019    16/01/2019

28/01/2019    30/01/2019

11/02/2019    13/02/2019

25/02/2019    27/02/2019

11/03/2019    13/03/2019

25/03/2019    27/03/2019

08/04/2019    10/04/2019

22/04/2019    24/04/2019

06/05/2019    08/05/2019

20/05/2019    22/05/2019

03/06/2019    05/06/2019

17/06/2019    19/06/2019

01/07/2019    03/07/2019

Answer 2

I'm afraid I can't figure out what you want the subquery to do. Can you provide some additional information about your tables and fields as well as what you expect the subquery to return?

Answer 3

I'm not sure where a subquery, or the DateDiff function comes into this.  To compute a series of recurring event dates the DateAdd function is more likely to be used, and it would be done by means of a loop rather than a query.  For instance to compute the start and end dates of a series of six 3-day events every two weeks, starting from known dates, the code would be along these lines:

    Const EventStart = #7/2/2018#

    Const EventEnd = #7/4/2018#

    Dim n As Integer

    For n = 0 To 5

        Debug.Print DateAdd("d", 14 * n, EventStart), DateAdd("d", 14 * n, EventEnd)

    Next n

This results in:

02/07/2018    04/07/2018

16/07/2018    18/07/2018

30/07/2018    01/08/2018

13/08/2018    15/08/2018

27/08/2018    29/08/2018

10/09/2018    12/09/2018

Note that the results are returned in the UK date format of dd/mm/yyyy on my system, though the date literals must be expressed in US short date format or an otherwise internationally unambiguous format.

Answer 4

Sorry about the Vagueness

I am trying to create a query that produces recurring events between two dates, EventStart and EventEnd. The frequencyTypeID is weather the repeating event is daily, monthly or yearly.

I can produce a query which gives all the recuring events if i have EventStart and RecurCount, which is the number of times the event recurs.

However this time i am trying to gain this RecurCount by perfroming subquery between the two dates, EventStart and EventEnd, using the DateDiff function. I then need to use the data from the DateDiff function as an alias field to produce all the recurring events.

Hope this clears up my question.

Thankyou very much

Answer 5

Could you explain what you want to DO with the subquery? Step back a bit and describe your current structure, and what specifically you want to accomplish. What are PeriodTypeID, EventStart and EventEnd?

My GUESS is that you want to use DateAdd rather than DateDiff but without knowing your actual goal that's just a WAG.