Query - Automatically change month in query field based on system date

[BPERELLA]

Hello. I have a form based on a query which gets the current month's value and uses it in the form to calculate a field. This is a multi-user database. and I have to remember to go into the database on the first of the month to change the query field used in the form calculation. I am struggling to figure out a way to do this automatically based on the current month in the system date. The field from the query used in the calculation is called "SumOfSumOfApr" which today I changed to
"SumOfSumOfMay" Thanks for you help.

This was my first thought which oviously doesn't work:

 

[Gasman]

Just call it SumOfMonth and use the Month() function on Date()

 

[plog]

Database normalization applies to queries as well:

Database normalization - Wikipedia

en.wikipedia.org

You need to set up your queries so they don't need maintenance. My guess is, come 1/1/2023 you're gonna have to edit a few queries to get this thing to work. If you make it such that the data you need to use (either the values or to apply criteria) are in fields, it makes life so much simpler.

Query1b should not have a field for every month, it should have a month field where you store that value. And a year field so that you never have to go into its design view again. Life in the future could be so much simpler if you set this thing up correctly now.

 

[Pat Hartman]

Here's what a generic query would look like. It won't ever need any maintenance.

Select Customer, Year(OrderDate) As OrderYear, Format(OrderDate, "'mmm") As OrderMonth Sum(Amt) as sumOfAmt, Count(*) As OrderCount
From tblOrderHeader
Where Year(OrderDate) = Year(Date()) And Month(OrderDate) = Month(Date())
Group by Customer, Year(OrderDate), Format(OrderDate, "mmm");

This query will always return the sum and count by customer for the current month.
A more flexible solution would be to have two controls on a form. One for year and one for month. You can set them to default to the current year and month as shown above but, they would be modifiable so you can call up last month if you want or a different month from three years ago.

Where Year(OrderDate) = Forms!yourform!txtYear AND Format(OrderDate, "mmm") = Forms!yourform!txtMonth;

If you want to choose a specific customer, you can add a third element to the criteria.