Month to Date when tied to a criteria

[WLC]

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]

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.

 

[WLC]

I do have a query that obtains the date for the report (it's the record source for the report). I have it set up where there's a parameter that I have to enter the date and that's what populates the report.

I'm just not sure how to tie the two together (the date that I enter in my parameter query and this subreport).

 

[Brianwarnock]

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.

Brian

 

[WLC]

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.