There will, no doubt, be other ways of doing this, but the following should work.
1) Construct the final string as you are now (e.g. HAHHHAAHA).
2) Make two copies of this final string
3) Use the Replace function to remove all letters H from one.
4) Use the Replace function to remove all latters A from the other.
5) Count the string lengths, using the Len() function.
6) Create your final answers in the form you want.
Hwyl, Arwyn! Hope things are going well back home.
I would create some VBA code. You can either put this in the On Click event of a button (i.e. click the button and see the figures you want) or in something like the After Update event of the last field to be completed (so the user fills in the final value and is shown the total). You coulod also, if you wanted, create it as a function and call it after any field is updated, ensuring a running total is displayed.
Something like:
Dim str_Overall as String
Dim str_H as string
Dim str_A as String
Yep, I believe so (and forgive me if I was being twp ).
I think the way I described would do the trick. Apologies in advance if I explain something you already know.
1) Create two fields on the form - if you haven't already done so - one to display the H value, one to display the A value.
2) Create a button on the form (for now, I'll assume you want to see the totals when you click on a button. If this isn't the case, you can pretty much copy and paste the code to wherever you do want to run it).
3) Behind the OnClick event of the button, you'll be creating an Event Procedure (something to occur when the button is clicked). Right-click on the button and, under 'Properties', go to the Event tab. Click in the row labeled 'On Click' and you'll see three dots appear to the right.
4) Click on those and choose 'Code Builder'. The code window will show a blank sub procedure which will contain the code to run whenever the button is clicked.
5) Copy and paste the following, but remember to change the line shown in bold itallics to include all of the fields you want to count (presumably Jan1 to Jan31):
Code:
Dim str_Overall as String
Dim str_H as string
Dim str_A as String
[I][B]str_Overall = [jan1] & [jan2] & .....etc.[/B][/I]
str_H = Replace(str_Overall,"A","")
str_A = Replace(str_Overall,"H","")
str_Final_H = Len(str_H) & "H"
str_Final_A = Len(str_A) & "A"
FieldForHResult = str_Final_H
FieldForAResult = str_FinalA
6) Assuming the two fields you created to display the results are called FieldForHResult and FieldForAResult, you're fine. If not, alter the last two lines of code to whatever the fields are really called.
7) Save the changes made and go back to your form. Enter some H and A values into various fields and click on the button. Assuming the code works correctly, you should see the reults you want. If not, you may need to play with the code a little, but hopefully you can get the gist of it from the above.
Any more questions, let me know.
P.S. One of the good things you fast realise about this forum is that, if I've told you something incorrect, somebody will soon point it out.
I think I see where I misinformed you.
Apologies for that, but glad to hear you were able to fix it.
Hopefully, this will be more successful.
1) Create a new field on the form (I've called it FieldForLResult)
2) Amend the existing code
Code:
Dim str_Overall as String
Dim str_H as string
Dim str_A as String
Dim str_L as String
Dim str_Final_H as Integer
Dim str_Final_A as Integer
Dim str_Final_L as Integer
str_Overall = [jan1] & [jan2] & .....etc.
str_H = Replace(str_Overall,"A","")
str_A = Replace(str_Overall,"H","")
str_L = Replace(str_Overall,"L","")
str_Final_H = Len(str_H) & "H"
str_Final_A = Len(str_A) & "A"
str_Final_L = Len(str_L) & "L"
FieldForHResult = str_Final_H
FieldForAResult = str_Final_A
FieldForLResult = str_Final_L
In each of the above cases, you should be replacing the other letters with a blank (""), not the letter you're searching for e.g. if creating str_H, you want anywhere an 'A' or an 'L' appear to be removed, not - as I wrote initially - anywhere an 'H' appears.
Consequently, you're seeing the number of all characters apart from H, as opposed to the number that are H.
This should fix it
Code:
str_H = Replace(str_Overall, "A","") ' --- Removes all As
str_H = Replace(str_H, "L", "") ' --- Removes all Ls
str_L = Replace(str_Overall, "A","") ' --- Removes all As
str_L = Replace(str_L, "H", "") ' --- Removes all Hs
str_A = Replace(str_Overall, "H","") ' --- Removes all Hs
str_A = Replace(str_A, "L", "") ' --- Removes all Ls
I think your life would have been easier if I'd just stayed out of it.
It took me a long time to get to the level I'm at - and that's not expert by any means.
).
