counting fields in query or on form

[AcornD]

i have several forms and queries one senario i do a query to find all the times a + as been placed in a field for a particular user i am trying to count thes and place the total at the bottem of a form is it possible and how?

 

[Blorf]

It is possible I think, but I have some questions, the Pluses (+) are the within a field, like "Hello + there"?

Also, are you counting them for one key, in one field, like this:

Name Field
Charles "Hello +"
Charles "++ r ++"

So for Charles, the total is 5?

 

[AcornD]

The fields are

Name Subject Succeed Fail
Charles Maths +
Charles Science +
Dave Maths +
Dave Science +
etc

And iv got to add up on a form


Name Succeed Fail
Charles (Total +'s) (Total +'s)

so for each person i have their total amount of Successes and failures.

 

[SuicidED]

To be honest your table is incorrect. If all you want to record is a pass or fail, all you need is one field as yes/no, yes being a pass no being a fail. However based on your present table this should work.

SELECT Table3.Name, Sum(IIf([Suceed]="+",1,0)) AS Passed, Sum(IIf([Fail]="+",1,0)) AS Failed
FROM Table3
GROUP BY Table3.Name;

 

[AcornD]

what it is we have ten fields that can have either a "+" "=" or "-" in the field and i have to no how many + every person has in total so that i can see at a glance on one sheet how well the pupils are coping with their work. ie if they score below 5 then that person needs help and looking at more closely.




so the sheet would look something like


Name Attainment Effort Progress Behaviour Homework

John 10 9 9 10 9
Mark 5 2 2 1 0

the teachers choose either a + = - and i need to do one of these tables for each.
Hope this clarifys wot im trying to do.

 

[SuicidED]

Ok that will work, have on your form two unbound text boxes one for succeed one for failed and have as their control source =Sum(IIf([Succeed]="+",1,0)) for succeed box and =Sum(IIf([Fail]="+",1,0)) for the failed box.

 

[PeteJohnston]

As a bit of lateral thinking, you could use the Split() function. It fills an array with strings seperated by a specified character. If the character isn't found it puts all of the string into the first element. Because the array is zero-based the size of the array is the same as the number of times the character is found.

Dim Pluses() As String, NumberOfPluses As Integer

Pluses = Split(txtBox, "+")
NumberOfPluses = UBound(Pluses)

You can put that code snippet in the _Change event for each text box and set the value of another text box equal to NumberOfPluses.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)