Year to Date Math

[tbeers]

Access 2007

I have a program that calculates time efficiencies,(charged hours vs billed hours) over a date range. The date range is entered in an input form. Works fine. My question is, can I find a way to add Year To Date calculations without having to actually enter the first day of the year in the date range. In other words, can I create a field on the report that automatically shows me total hours worked Year To Date?

Thanks


-Tomb

[TommyGunn]

Here's a site with some sample date manipulation. You would just need to pass it the current year from the current date. Hope it helps.

http://www.cpearson.com/excel/DateTimeVBA.htm

[NeoPa]

I expect so Tom.

What exactly is your algorithm for deciding hours worked Year-to-Date?

[NeoPa]

PS. Did you realise when you chose TomB as your signature that it has an alternative, more morbid, interpretation?

[tbeers]

PS. Did you realise when you chose TomB as your signature that it has an alternative, more morbid, interpretation?

Actually, I am not aware of a more morbid interpretation....interesting.

At any rate, I have a report that computes various totals of data based on a date range that is entered by the user. I would like to include a YTD calculation without any addtional user input.

TomB

[Stewart Ross]

Hiya TomB. It is certainly possible to do year-to-date calculations using a totals query to do so - but Access does not of itself know what year you may mean, so you need to store these details in a table. I do this for order processing, staff hours and other applications where the year is not the same as the calendar year; I store the year reference, the start date, and end date, and then join the date table to the relevant totals query where the dates of interest are between the start and end dates listed.

In the UK financial years typically run from 1 April to 31 March, but this is just a convention; the financial year of the college where I work actually runs 1 August to 31 July. The advantage of using a table to store such dates is that you can then use the dates stored for particular years to select the transactions that apply between these dates to view end-year totals, totals to date and so on.

(And by the way, NeoPa's comment about the connotations of your name resulted from signing yourself Tomb in post #1.)

-Stewart

[NeoPa]

Stewart correctly interpreted the "morbid" reference, although having the 'B' in lower case would not have been necessary for the link (In case there's any doubt , I'm not poking fun at you or your name) ;)

Anyway, let us know if you have this sorted now. If not, explain clearly where you're stuck.