Query - Automatically change month in query field based on system date (1 Viewer)

BPERELLA

New member
Local time
Today, 06:11
Joined
Feb 10, 2022
Messages
7
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:

1651509541994.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:11
Joined
Sep 21, 2011
Messages
14,050
Just call it SumOfMonth and use the Month() function on Date()
 

plog

Banishment Pending
Local time
Today, 05:11
Joined
May 11, 2011
Messages
11,613
Database normalization applies to queries as well:


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

Super Moderator
Staff member
Local time
Today, 06:11
Joined
Feb 19, 2002
Messages
42,981
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.
 

Users who are viewing this thread

Top Bottom