Date Range in Query (1 Viewer)

Sephanie

New member
Local time
Today, 11:26
Joined
Apr 15, 2020
Messages
5
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

MVP / VIP
Local time
Today, 16:26
Joined
Jan 14, 2017
Messages
18,186
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

New member
Local time
Today, 11:26
Joined
Apr 15, 2020
Messages
5
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

AWF VIP
Local time
Today, 11:26
Joined
Apr 3, 2020
Messages
1,003
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
 

Sephanie

New member
Local time
Today, 11:26
Joined
Apr 15, 2020
Messages
5
@isladogs
I'm probably to tired but I can't find the example app you talking about.
Never mind. Found it.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 16:26
Joined
Jan 14, 2017
Messages
18,186
Sorry, I should have said you needed to scroll down the page to find it.
 

Sephanie

New member
Local time
Today, 11:26
Joined
Apr 15, 2020
Messages
5
@ 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.
 

Users who are viewing this thread

Top Bottom