Month/Month to date

[Tony McGuire]

I have created a data base which includes the 12 months of each year. For each month I have created 2 forms based on 2 seperate queries. Both forms contain the same 4 fields i.e. 'DueDate - Income - Expense - Balance. To the first form I have, under DueDate on the query side written the following criteria: Between 01-01-2011 and 31-01-2011 (the month of January) this shows all expected posts for the month of January for the complete month.



For the second form i have changed the query wording slightly to read:
Between 01-01-2011 and Now() this shows the posts from day to day as the month progresses however this can run into February and so on unless i change the criteria reading.

Is there any way I can word the query so that it stops automatically at the end of the month?

Tony McGuire

[June7]

Maybe, if you can answer this question: At the end of which month?

What you describe seems more like what should be done in a report - grouping data by month.

[ssanfu]

From what little you have posted about your mdb, it sounds like you might have a table for each year. This would be a bad design; all years should be in one table. Also, since the balance can be calculated at any time, you shouldn't store the balance in a table.

To answer your question, in a query, add two columns. In the new columns add:

Field:       TheMonth: Month(DueDate)    TheYear:Year(DueDate)
Criteria:               1                          2011

If you wanted all entries for Jan, the criteria for TheMonth column would be 1 and the criteria for TheYear would be 2011.

[June7]

Good catch, ssanfu, on the data structure. On re-read I can see that also. Agree, bad design.

[Tony McGuire]

June,

Do you report these posts? If not, you should be doing it. The amount of posts that show up in these FAQ forums is ridiculous. It is unneccessary clutter, and they need to be moved.

thanks.

Thanks for your help which was NO help. You should learn to suffer fools in silence. If anything should be removed then MAYBE it's you. You may be an expert but we beginners have to learn from the bottom! Do you take your lead from "June 7"?

Have a nice day
Clutterbug

[Tony McGuire]

Good catch, ssanfu, on the data structure. On re-read I can see that also. Agree, bad design.

Maybe I'll learn in time about better designing etc,

Anthony McGuire

[Tony McGuire]

Thanks for your quick reply.

[Tony McGuire]

Maybe, if you can answer this question: At the end of which month?

What you describe seems more like what should be done in a report - grouping data by month.

Does it really matter which month I am referring to?

[June7]

What criteria determines the 'end of the month'? You state about the second query 'this shows the posts from day to day as the month progresses'. Do you want this query left alone? Is it the first query you want to automate? You can always calculate the previous month last day from the current date. Like: DateAdd("d",-1,CDate(Month(Date()) & "/1/" & Year(Date()))) or CDate(Month(Date()) & "/1/" & Year(Date()))-1 because the day interval is default. That what you are looking for?

[Tony McGuire]

Your answer credits investigation I wii try it and get back to you with the result.
Thanks