Basic Counting

[chad1222]

I am having issues with the expression builder trying to create a simple count. I have a table with a field called "switch type" i am trying to make a form with a text box that displays the current count of each type. How do i create this with the expression builder. What i keep trying is making the text box = =Count([2950-24 Query]![Switch Type])
but it just says error in the box.

nothing i try is working. The query in there is just a one column that includes only 1 type of switch and i have 3 query's that do the same thing for each "switch type". I thought counting the query would be simple but its not working. Any help would be appreciated.
Chad

 

[Cbrine]

So,
You have 1 form that needs to display the results of 1 query? or
you have 1 form that needs to display the results of 3 queries?
If it's 1 query, you should be able to associate the form data source to your query, and select the field you want to display from the field selection box.
If it's 3 queries, then you will need to do some VBA.
1. You need to create a unbound form, and add 1 textbox for each results you want to display.
2. You will need to create a vba macro that use DAO, ADO to extract the results of each query and store theses values in the forms textbox.
3. Then refresh the form, and Viola, you have the data on the form.

Hopefully it's just one value.

 

[chad1222]

Well lets see if i can answer your question. I have a form that i have called "Totals". Right now i have 6 labels and text boxes in that form. the first 3 text boxes will be manually filled in with the number that was originally in inventory. IE. the number of an item that we had to begin with.
the 2nd set of 3 text boxes i want to know whats left in inventory right now. So it needs to take the value in one of the first 3 boxes and subtract from that the number of that particular item that has been entered into the Dbase. So if i have entered in 15 items in the dbase and i goto that form i want it to tell me that i had 199 to begin with and now i have 184 left.
I guess my problem is that when i bind the textbox that i want to show current totals in to a query it gives me an error and when i try to build an expression that counts the results of a query it gives me an error. It doesnt seem that hard to do. I do not know if its my querys that are the problem. Basically the querys just give me list of all the same items from another table it does not give me the total its just a list of all the items. Maybe that is my problem. How do i create a query that will give me a number instead of a big list of the same item.

 

[Cbrine]

To get a total of items you will need to do this.

1. Add the Item Number field to the query design grid.
2. add the Item number field to the query design grid(yes, again)
3. Right click on the grid and turn on totals.
4. Adjust the totals of the second field to reflect Count
(This assumes you want of count of the inventory and not a sum of the quantity)
This should give you a count of all items.

I'm to busy today to help out much more, but I will be back next week.

PS-Try working with an unbound main form and your query setup on a sub form. Do all your calculations in the main form.

 

[bat17]

I would also have a look at using Dcount() as well

Peter