Count Query Results in Form Textbox

[ImASig]

Here is what I am trying to do:

I want to count the number of Type 'A' and Type 'B' orders I have made per month and display it on a form.

I know I can easily make a query to calculate each individual month and display the results of that query in a form. But I would like to include multiple months on a single form which makes me think that it would be best to do this in VB and throw all type 'A' and 'B' orders into one query with the date field included. The VB would search this query and display the results in the text boxes as I program them for each individual month.

I'm not the greatest programmer but I know the basics. If someone could provide me with some sample code for displaying a count of each order, based on a query, for a month in a text box. Or if I'm going about this the wrong way then any other suggestions would be much appreciated.

 

[willir]

ImASig

I am not what you are specifically looking for.

If you are going to display multiple calculations, you can do it "grunt style"

The ControlSource for each field could look something like...

=DSum("[YourNumberFld]", "YourTable", "Month(YourDate) = & YourMonthVariable & " And Year(YourDateFld) = " & YourYear)

You can also use a similar approach with VBA coding...

lngSum1 = DSum(....

Perhaps a slicker approach might be to use a loop and load an array with the sums / calculations, and display the components of the array.

Richard

 

[ImASig]

Richard,

Thanks for the reply. We're a little off of what I'm needing but I might be able to work off this.

In the mean time, let me explain better what I'm doing. I'm trying to count the amount of new customers entered into our database for each month. Our new customers are identified by two order types, again say Order Type A and B. However we also place numerous other orders on existing customers stored in the same table. For that reason I have made a query that extracts both orders A and B with the date the order was placed included in the query results.

Now what I am needing is to have a form divided up into sections per month. I want to have two text boxes for each month displaying the total for orders A and B in each month. So in my mind this is the psuedo VB code I'm imagining on the text boxes.

Count (Query.GetAllNewOrders.Type)
Where Type = 'A' AND Date = BETWEEN 7/1/04 AND 7/31/04


Does this help explain what I'm trying to do?

 

[ImASig]

Ah Hah. I've found a better way to do it. I just made a crosstab to sort by month. Thanks Richard for helping out.