Calculare YTD from First Sunday in March 1

[JDRoss]

I am trying to produce stats. Unfortunately, for this the year does not begin as a calendar year, it begins on the first Sunday of March (ends on the last Sunday of February).

Can anybody advice me how to calcalate totals for YTD dynamically for any given year?

Much Appreciated!

Regards

John

 

[MichaelRed]

The first sunday of march seems do-able w/o knowing a lot about the financial issues. As Always, the following incldes NO error trapping and probably not enough validation of the input arg. The USER is expected to do all of that messy stuff somewhere PRIOR to calling this.

Public Function basFirstMarSun(Optional DtIn As Variant) As Date

    'Michael Red    11/9/02     'First Suncday in MARCH
    'Tek-Tips thread705-410203

    '? basFirstMarSun
    '3/3/02

    '? basFirstMarSun("Jun 12, 2002")
    '3/3/02

    Dim CurDay As Variant
    Dim MyDt As Date

    'Verify that we have something to use for a date
    If (IsMissing(DtIn)) Then
        MyDt = Date
     Else
        MyDt = DtIn
    End If

    'Get the March 1st date
    CurDay = DateSerial(Year(MyDt), 3, 1)

    'First, check it is NOT already Sunday
    If (Weekday(CurDay) <> vbSunday) Then
        CurDay = DateAdd(&quot;d&quot;, 8 - Weekday(CurDay), CurDay)
    End If

    basFirstMarSun = CurDay

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JDRoss]

Thanks for the Function, basFirstMarSun. It will be very useful.

To give you further details of the challenge.

I have users input collection/contributions based on Month and year. E.g. I give them a form to select the month they want to enter contributions for (as in practice this work tends to be batched), they also enter the year. e.g. November, 2002.

Then the user is taken to the Collections entry form, the details of month and year are taken from the first form to populate for each Parishoner they they enter amounts for in an entry session. I also trap duplicates based on month, year, contribution no.

My problem with stats is that YTD needs to calculate for e.g. March 2002 - Februry 2003, as a liturgical year, etc.

I am not sure I have logically gone about this the right way?

I'd appreciate your advice

Regards

John

 

[MichaelRed]

the 'right way' is mostly a selection of 'possible ways' (at least in Ms. A.). Advice on any process can only usefully (in my opinion) be related to the level of detail known about the process.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JDRoss]

Thanks Michael

the 'right way' is mostly a selection of 'possible ways' (at least in Ms. A.)

I think, I understand what you mean.

Once again, thanks!

John

 

[PaulBricker]

John, a little more information will help. How is the data input. You say by month and year, March 2002 or June 2002 etc. But do you have two different fields, Month and Year, or is the Field datatype text so you can just enter March 2002. Some of that info will help.

Paul

 

[JDRoss]

How is the data input?

The database keeps track of regular contributions to a Parish fund. People who contribute to this fund do so on a weekly basis by envelope. Each contributor has his/her own unique envelope number.

A record of the contributions is done every month. My table keeps track of the transaction data on two integer fields, one is Monthid and the other yearid. This is a sample:

ID MonthID YearID
3 7 2002
5 7 2002
6 7 2002
8 7 2002

The month numbers come from a table, these are per usual 1-12. Even though the Year starts on the First Sunday of March and ends on the last Sunday of February of the following year, I represent the monthid as a calender month number, so January=1, February=2, etc.

I am trying to calculate a YTD report for each contributor based on the two fields above, monthid and yearid.

So for 2002. I need to calculate

between
MonthID=3 and yearid=2002 AND Monthid =2 and YearID=2003


But I need to do this dynamically, because the database is expected to show totals back 5 years.

I have been toying with the idea of using:

CVDate(Format$(##,"mmm yyyy&quot)

to convert the above two fields to a combined date field and then doing the calculation with Datepart. And then DateAdd

I haven't successfully coded this. Or maybe I don't need to convert to date at all. If I can concate the two fields as an integer, could I do a query based on that instead?

I hope this is sufficient information on the whole problem. I am willing to reconsider other logic if there is an easier way?

Regards

John

 

[MichaelRed]

Given the desired 'start date' (basFirstMarSun) you should be able to reasonable easily be able to select the records as between this date and what ever other date you want ~~~:

Between basFirstMarSun([SomeDate]) and [TheOtherDate]

[tab][tab][tab][tab]OR

Between basFirstMarSun(#1/1/98#) and #21/31/02#


The square brackets would indicate a PARAMETER which would be input by the used, or a field from the recordsource for the query.

Again/still, I do not know the details of your app, but am just attemting to illustrate a method to obtain (and, here, use) the date calculation.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PaulBricker]

John, assuming you have an Contribution field in there someplace as well, try this
Create a query using your Table as the Record Source. Then add the ID field (I assume this field is the envelop number field), and the Contribution Field. In a new column put this expression
LY: IIf([MonthId]>=3,[YearId] & " To " & [YearId]+1,[YearID]-1 & " To " & [YearId])

This will produce Liturgical years for your YTD value.
Then turn on the Totals button. The big Greek looking E. GroupBy ID the the field LY, set the Contribution field to Sum. This should sum the amounts contributed by each ID based on LY (Liturgical Year).

Paul

 

[JDRoss]

Thanks Paul

I found your:

LY: IIf([MonthId]>=3,[YearId] & " To " & [YearId]+1,[YearID]-1 & " To " & [YearId])

just the thing I was looking for.

It works. I have entered some different years ahead and it is totalling just fine.

Once again, thanks to you guys for all your help. I'm sure I'll be back for more soon.

Regards

John

 

[PaulBricker]

Excellent. Glad it helped.

Paul