Date Range in Query

[Sephanie]

I'm trying since days to find the right formula with no luck. I have a form called Westwood Night Audit this forms record source is a query with a date field named CHQ#_Acc. I need the query to return a Month to date summery. Like show summery from the first of the month (CHQ#_Acc) to the date in the field.

I tried so many different formulas and nothing worked. I got it to show me the summery for the month till today but I need it to be able to show the summery for a certain day, say I need to see what was the MTD summery for the 10. of the month but today is already the 15.

Thank you in advance for your help.

 

[isladogs]

Welcome to the forum.

Use an aggregate query by clicking the Totals button in the ribbon.
Group by Date and any other fields as appropriate. For the summary fields, change Group By to e.g. Sum or Count.
If you want a specific date only, filter the query for that date

 

[Sephanie]

Hi isladogs, thanks for the quick answer. I already did the first part. I don't want to filter since the people using this need it as easy as possible. So I would like to tell the query that it always starts at the first of the month and sum all the way to the date in the field.

I tried with

Between [Forms]![Westwood Night Audit]![StartDate] And [Forms]![Westwood Night Audit]![CHQ#_Acc]

StartDate is a field that value is set to the first of the month. But it doesn't work.

 

[strive4peace]

hi Sephanie,

if [StartDate] is an unbound textbox, Access doesn't know it is a date and not text, try wrapping the reference with DateValue or INT (my preference) so Access makes it a number and doesn't treat it as text

DateValue([Forms]![Westwood Night Audit]![StartDate] )
or
INT([Forms]![Westwood Night Audit]![StartDate] )

if it is a field in your table, make sure the data type is date/time. Same comment for CHQ#_Acc

If it is a calculated field in the record source, you'll also need to wrap it with a conversion function so that Access treats it as a date

 

[isladogs]

@Sephanie
See if my example apps on grouping and filtering help.
In particular, look at the second example as it includes handling date ranges Incident Analysis

 

[Sephanie]

@strive4peace
Thanks for your help, both fields are bound fields from a table and set to date/time.

 

[Sephanie]

@isladogs
I'm probably to tired but I can't find the example app you talking about.
Never mind. Found it.

 

[isladogs]

Sorry, I should have said you needed to scroll down the page to find it.

 

[Sephanie]

@ Isladogs
I looked at it, but I don't think that is what i am looking for. But thanks for the tip I found other things on your website I will definitely look more into it.