Calculate MTD

[WLC]

I posted this yesterday and didn't seem to get quite the answer I was looking for so I'm reposting.

My original post: How do you calculate or find Month To Date in a query as it relates to a hand keyed criteria.

For example I have a field called Operating Day and right now my criteria in my query is Between DateSerial(Year(Date()),Month(Date()),1) And Date(). Works perfectly. I'm using this data in a sub report.

But now my requirements have changed and its possible that I may need to report on something from January (or December and so forth). Well this criteria will show data from February. But the Month To Date data should show totals for the Month I'm reporting on.

Is this even possible?

pr2-eugin suggested: Yes, obtain the Date as a Parameter from the user, then replace the Date() with the Parameter name. So every time you open the Query you will be prompted to enter this parameter.

I'm doing this from a query so I don't have a parameter name.

Brianwarnock suggested: What does the user enter if he wants December 2013? 1/12/13 ( in UK format)?

to get all of the month

Between Dateserial(Year(param),month(param),1) and Dateserial(year(param),Month(param)+1,0)

If the param is supplied from a date formatted control on a form then the first Dateserial can be replaced with param, I think.

I have assumed that you have no data passed todays date.

To which I replied and attached the files: The report is based on one day so the user would enter 1/12/2014 (January 12th, 2014) from this query (attached image EnterDate.jpg).

Then there is a subreport from the main report that runs this query (attached image MTD.jpg).

And you are correct in your assumption that I have no data past today's date.

Can someone please assist?

 

[pr2-eugin]

Are you trying to [Enter Date] say 01/01/2014 and get the Report for starting from 01/01/2014 till 06/02/2014 (i.e. Date()) Or, 01/01/2014 till 31/01/2014?

 

[WLC]

I would like to [Enter Date] 01/12/2014 (January 12th, 2014) and get 01/12/2014 through 01/31/2014 (January 1st, 2014-January 31st, 2014)

 

[WLC]

***REVISED***

I would like to [Enter Date] 01/12/2014 (January 12th, 2014) and get 01/01/2014 through 01/31/2014 (January 1st, 2014-January 31st, 2014)

 

[pr2-eugin]

You need two functions. Copy the following into a Standard Module, and then save and Compile.

[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
Public Function getMonthStart(inputDate As Date) As Date
    getMonthStart = DateSerial(Year(inputDate), Month(inputDate), 1)
End Function

Public Function getMonthEnd(inputDate As Date) As Date
    getMonthEnd = DateSerial(Year(inputDate), Month(inputDate) + 1, 0)
End Function

Then use them in the Query as.

SELECT theTable.AllTheFields
FROM theTable
WHERE theDateField BETWEEN getMonthStart([B][EnterDate][/B]) AND getMonthEnd([B][EnterDate][/B]);

 

[WLC]

This worked perfectly. Thank you.

Could you possibly help me with another thread?

http://www.access-programmers.co.uk/forums/showthread.php?p=1328767#post1328767

 

[pr2-eugin]

No problem. I will have a look, but plog has started helping you out. I will watch the thread, if I can be of any help I will post it there !

 

[WLC]

plog hasn't posted anything since Feb 3rd. I posted a reponse on the other thread. Thanks for your help!

 

[Brianwarnock]

Why does he need functions, the formula I gave him would work directly in the query. It was Paul's two Dateserials coded directly into the criteria.
Why will that not work?

Brian