Using system month to limit display of query? 1

[raybas]

I have a databse in Access in which I need to know how much of a product I sell every month. I would like to be able to see the amount I have sold for the current month on the order form everytime a new order is placed or an ordered altered. I have created a query that shows the sales for each month, but I am not sure on where to go from there if this is even possible. Every month I want this to change to the present month to reflect sales for that month.

Thanks,

Raymond Basaldua
Senior Systems
Arizona State University

 

[dawnd3]

I am not sure of the format of your date feild, but here are a couple of options.

In the criteria for the date field type: Between [enter beginning date] and [Enter ending date]

Or type: [Enter month]

Both will trigger a parameter query and ask the user to imput the criteria every time the report is ran. Once you finish the query, create a report with the query as the record source.

Note: you can pretty much put whatever you want in the [brackets] as long as it isn't the actual field name.

Let me know if this helps. Dawn

 

[raybas]

dawnd3

that was helpful, however I am not just wanting to create a general report. What I actually want to do with the querried month data is display in the order form. That is, when somone opens the database and opens the ORDER FORM they will see a number in a box that represents the sales for that moth of the one product we sell. I am wondering if there is a way to automate access to enter in the current month and year as the criteria. I can clarify further if needed. It woldn't matter if the box with the number was a form or something that opened up when the the databse is opened. Kind of like the switchboard box automatically opening up but this would display the number.

Thanks again!

 

[dawnd3]

Well I guess this is where I am stuck too. I know there is a Month() function that returns the number of a month for a date, so if there was a way in VBA to say "Check all the dates and total all the sales amounts for the dates within the current month, and then display in the field on my form". Or possibly in the control source of the box on your form use SUM (sales amounts) where month([date field])=month(Date()) (Date() Being current system date) Of course I am probably WAY off on the syntax here. Anyone, anyone????

 

[Maquis]

I think this may work. You said that you already have a query showing sales per month. Put a field in that query which returns the sales date in just a month/year format like this:
SalesMonth: format([SalesDate],"mm-yyyy&quot

and then the criteria box enter:

=format(Date(),"mm-yyyy&quot

This should limit the query to return only the sales for the current month.

I hope that works for you. B-)

 

[braindead2]

if you want to do as you say
"see a number in a box that represents the sales for that moth of the one product we sell"

I suggest you look at the dsum() function
it will lokk something like

dsum("[field that holds order]","Tablename",Month(flddate) = date() and Year(flddate)= date

 

[JoeMiller]

You may be able to use a DSum to get what you're after as well. I'm going to assume that you have a table called Sales which contains a ProductID and a Quantity sold. You can inspect that table based on the current ID and the correct date like this:

=DSum("[Quantity]","Sales","[ProductID]=[txtProductID] AND Format([SaleDate],"mmyyyy&quot=Format(Date(),"mmyyyy&quot&quot

That statement would go to the Sales table and sum up the quantity of the ProductID in the control txtProductID where the SaleDate field (in mmyyyy format) is equal to the current date (in mmyyyy format).

HTH Joe Miller
joe.miller@flotech.net

 

[raybas]

Dawnd3,

your solution worked out just great. I appreciate all help on this. I do however have another question now. Is there a way to force a screen to refresh to update with a current number of sales. When I go to the CUSTOMER form I select ORDERS to place an order. When I close ORDERS I am back at the CUSTOMER form and that is where I have the number of sale posted on the corner of the form. It doesn't not show the accurate number unless I manually refresh the screen. Any suggestions?

 

[Maquis]

you could put me.refresh in the on got focus event of the form.

 

[JoeMiller]

textboxname.requery

This would work better as this will only refresh the control instead of refreshing the whole form.

HTH Joe Miller
joe.miller@flotech.net

 

[raybas]

The problem I ave though is that the customer form stays open behind the order form. So when I close the order form I am already at the customer form. There is no way I can see for the customers form to refresh or requery. I would think when I close the order form I need to have it refresh all forms or something. This is where I am stuck.

 

[Maquis]

I'm assuming that the user will click on to the customer form somewhere after closing the order form. If so, then the 'got focus' event will fire at that time. Just put the refresh or textboxname.requery statement in that event.