VBA dates on a form made from a query

[sdel_nevo]

Hi all



i have been struggling for a while with this problem, and can't get my head around how to solve it.

I have a form called "this months out goings by supplier" that lists all of our outward spend by category and supplier, this is listed by entry date and the transaction amount.

on this form is a pie chart that plots in graphical form the payments to each supplier.

this form is created from this query called Expenses

SELECT [Transactions Extended].[Entry Date], Month([Entry Date]) AS [Income Date], [Transactions Extended].Payment, [Transactions Extended].Categories.ID, [Transactions Extended].[Income/Expense], [Transactions Extended].Taxable, [Transactions Extended].Code, [Transactions Extended].[Account Description], [Transactions Extended].[Default VAT Rate], [Transactions Extended].[Display in Sales], [Transactions Extended].[Display in Purchases], [Transactions Extended].[Account Transactions].ID, [Transactions Extended].[Entry Title], [Transactions Extended].Category, [Transactions Extended].[Total Payment], [Transactions Extended].[Recipt Attachment], [Transactions Extended].[Actual Amount], [Transactions Extended].[Entry Number]
FROM [Transactions Extended]
WHERE (((Month([Entry Date]))=Month(Now())) AND (([Transactions Extended].Payment)>0) AND (([Transactions Extended].Categories.ID) Not Like 86) AND (([Transactions Extended].[Income/Expense]) Not Like "Revenue") AND ((Year([Entry Date]))=Year(Now())));

this works really well and im very happy with it, except i can't find a way of moving back and forth between the months except for setting my computers date "that's not very convenient"

what i would like is a way that i can by clicking buttons on the form show last months results, or if i click the button again show the month before that to.

i think that i may have to add the query to a buttons on-click event, but i can't get my head around how to put the query into VBA.

any help would be most appreciated

Many Thanks

Steve

[amrut]

Try this -
Add a textbox say txtEntryDate to your form with format set to "Short Date" and default value to Date(). Modify the query replacing "Now()" with "Forms!YourFormName!txtEntryDate" Change the date as per your choice and requery the form on click of a command button.
If you always prefer to view data by month, I would suggest to use two comboboxes, one for year and one for month.

Edit :
Alternate method -
Controls - txtEntryDate as above,two command buttons to move back and forth between months. Add or deduct 1 month to the value of this text box and requery the form

[sdel_nevo]

Hi Amrut

many thanks for the reply, i have just noticed that its a report not a form opps!!! will this matter to the above?

Steve

[amrut]

Nope, open the report in the click event of command button on this form. Then you need only a single button as mentioned in the first method.

[sdel_nevo]

Hi amrut

i am having probs with this, i think im getting confused with it, could i send you a zipped database? perhaps im doing something really silly but i can't seem to get it to work

[amrut]

First check whether the query is yielding correct results. Post your db so that others can help too.

[sdel_nevo]

Hi

i have attached my database, i can't for the life of me get this working the report "Expenses by Category" has a form on it called "Expenses chart" that shows the pie graph of all of this month's outgoings.

this runs correctly from the "expenses" query.

what i can't get to work is this

i have a textbox on the report called txtdate, that i need to set to the current month and year.

once this is done i will change the criteria of the "entry date" within the expenses query to the text box on my "Expenses by Category" report.

im guessing that this will then allow me to create two buttons that i can use to change the dates as described above.

i can't for what ever reason set txtdate on the report to the current month and day!!! is this because the textbox for reports does not have a default value field?????

any help would be most welcome

Steve

[amrut]

Never used controls that need user interaction on reports - like command buttons, text boxes to input data
Always used forms,all parameters for querying/filtering through forms only

I do not know the user interface and structure of your application. Typically you will need a separate form to enter the date you desire and command button to open report. Use that date on form for criteria in query. Somebody may have a better idea .

In case of crosstab query, the parameter needs to be added. The typical SQL looks like -

 PARAMETERS Forms!Form1!txtDate DateTime; TRANSFORM Sum(Expenses.[Actual Amount]) AS [SumOfAccount Transactions_ID] SELECT (Format([Entry Date],"mmm"" '""yy")) AS Expr1 FROM Expenses GROUP BY (Year([Entry Date])*12+Month([Entry Date])-1), (Format([Entry Date],"mmm"" '""yy")) PIVOT Expenses.[Account Description];

Open report in command button's click event. hope this solves your problem.

[sdel_nevo]

Hi amrut

been pulling my hair out with this, i think i will put this problem to one side and move on with my application. i will come back to it later when i have got my head around it better.

Many thanks for your help

Steve