I am trying to make a query to count text as a figure. I have entered on a form

H = Hoilday

A = Absent

Jan 1 Jan 2 Jan 3
H A H

If you make a query to add [jan1]+[jan2]+[jan3]

The result is HAH but I want it to be 2H 1A

Any ideas? It might not be a query I need!

Thanks
Arwyn

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.

For example:

str_Overall = HAHHHAAHA
str_H = Replace(str_Overall,"A","") --- gives 'HHHHH'
str_A = Replace(str_Overall,"H","") --- gives 'AAAA'
str_Final_H = Len(str_H) & "H" --- gives '5H'
str_Final_A = Len(str_A) & "A" --- gives '4A'

Hope that helps.

Thanks for your reply to my problem.

But could you please tell me how I use the string?

Is it in the Criteria of a query, if so is it a select query.

By the way I am from Llanelli.

Thanks
Arwyn

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

str_Overall = [jan1] & [jan2] & .....etc.

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

Hope that helps.

Thank you for trying to help me. What I am trying to do is to difficult for me to complete.

Thanks
Arwyn

In what form do you need the end result?
Are you trying to see the 5H 3A bits on a form, or on a report, or send the results to a table, or what?

And everything's hard until you know how to do it. ;) It took me a long time to get to the level I'm at - and that's not expert by any means.

I have a form. It shows January to June. It is an attendance record for my employees.

For example

On my form employee Jane has decided to have this week as a hoilday.

Therefore on my form

Nov6 has h
Nov7 has h
Nov8 has h
Nov9 has h
Nov10 has h

But on November 1 she was absent from work, an a was entered in the box.

I do a Update query to add all the boxes Nov1 to Nov30 which will give a result of ahhhhh

What I want is 2 boxes giving me the result

in box 1 = 5h

in box 2 = 1a

Hope this helps

Thanks
Arwyn

Yep, I believe so (and forgive me if I was being twp :D ).

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):

Dim str_Overall as String
Dim str_H as string
Dim str_A as String

str_Overall = [jan1] & [jan2] & .....etc.

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 have tried what you said but it did not work. So I am leaving the problem for today, try again tomorrow.

Thank you for all your help

Arwyn

Matt

I have got your code to work perfectly.

But now I want to add another subject therefore

L = Late
A = Absent
H = Hoilday

Jan2 = L
Jan3 = A
Jan4 = H

I want 3 boxes on my form showing

Late = 1
Absent = 1
Holiday = 1

I am grateful for your help

Thanks
Arwyn

I think I see where I misinformed you. :o
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

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


Fingers crossed.

Thank you for helping me again.

The new code that you have given me comes up with an error.

The error is in line

str_Final_H = Len(str_H) & "H"

Any ideas?

Thank you
Arwyn

Thank you for helping me again.

The new code that you have given me comes up with an error.

The error is in line

str_Final_H = Len(str_H) & "H"

Any ideas?

Thank you
Arwyn

Perhaps:

str_Final_H = Str(Len(str_H)) & "H"

If you still get no luck, try putting

Msgbox str_H

Just before the problematic line of code, so that you'll be able to see exactly what the variable str_H is set to.
What exactly is the error?

Error is

Str_Final_H = Len(str_H) & "H"

I have tried what you suggested but the error comes up

Run Time Error 13

Type Mismatch

Thanks
Arwyn

I believe the problem is caused by trying to combine len(str_H), which is a number, with 'H', a letter.

Perhaps

1) Alter the declarations, so that

Dim str_Final_H as Integer
Dim str_Final_A as Integer
Dim str_Final_L as Integer

become

Dim ll_Final_H as Long
Dim ll_Final_A as Long
Dim ll_Final_L as Long


2) Alter the following from

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

to

ll_Final_H = Len(str_H)
ll_Final_A = Len(str_A)
ll_Final_L = Len(str_L)

FieldForHResult = str(ll_Final_H) & "H"
FieldForAResult = str(ll_Final_A) & "A"
FieldForLResult = str(ll_Final_L) & "L"


I'm sorry I don't have time to duplicate the problem here, as I'm a bit busy today, but hopefully this will help.

Hi Matt

on my form I have

2H
1L
1A

I changed your code to

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 = [jan2] & [jan3] & [jan4] & [jan5]

str_H = Replace(str_Overall, "H", "")
str_A = Replace(str_Overall, "A", "")
str_L = Replace(str_Overall, "L", "")

str_Final_H = Len(str_H)
str_Final_A = Len(str_A)
str_Final_L = Len(str_L)

fieldforhresult = str_Final_H & "H"
fieldforaresult = str_Final_A & "A"
fieldforlresult = str_Final_L & "L"

This gives me a result of

2h this is correct
3L (should be 1L)
3A (should be 1A)

Any idea what is wrong?

Thanks
Arwyn

Yep *sigh* you're suffering for my stupidity. :(

This section is all wrong

str_H = Replace(str_Overall, "H", "")
str_A = Replace(str_Overall, "A", "")
str_L = Replace(str_Overall, "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

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. :o

Dear Matt

The last code works perfectly.

Thank you for your time and effort.

Thank you
Arwyn

Str_Final_H = Len(str_H) & "H"

what this is doing is returing the lebgth of the string of H's {len(str_H)} and appending to it the letter H

the error is because you are trying to add a letter to a number.

so to convert something to a string you want cstr so its probably

Str_Final_H = cstr(Len(str_H)) & "H"

Thanks for your help but it did not work.

Please look at my new message for my next problem.

Thanks
Arwyn

Arwyn, just to confirm, is all now working or is there still a problem?

Hi Matt

I now have a code

Dim str_Overall As String

Dim str_H As String
Dim str_A As String
Dim str_L As String
Dim str_E As String
Dim str_S As String

Dim str_Final_H As Integer
Dim str_Final_A As Integer
Dim str_Final_L As Integer
Dim str_Final_E As Integer
Dim str_Final_S As Integer

str_Overall = [Jan2] & [Jan3] & [Jan4] & [Jan5]

str_H = Replace(str_Overall, "A", "")
str_H = Replace(str_H, "L", "")
str_H = Replace(str_H, "E", "")
str_H = Replace(str_H, "S", "")

str_A = Replace(str_Overall, "H", "")
str_A = Replace(str_A, "L", "")
str_A = Replace(str_A, "E", "")
str_A = Replace(str_A, "S", "")

str_L = Replace(str_Overall, "H", "")
str_L = Replace(str_L, "A", "")
str_L = Replace(str_L, "E", "")
str_L = Replace(str_L, "S", "")

str_E = Replace(str_E, "H", "")
str_E = Replace(str_E, "A", "")
str_E = Replace(str_E, "L", "")
str_E = Replace(str_E, "S", "")

str_S = Replace(str_Overall, "H", "")
str_S = Replace(str_S, "A", "")
str_S = Replace(str_S, "L", "")
str_S = Replace(str_S, "E", "")


str_H = Len(str_H)
str_A = Len(str_A)
str_L = Len(str_L)
str_E = Len(str_E)
str_S = Len(str_S)

fieldforhresult = str_H & "H"
fieldforaresult = str_A & "A"
fieldforlresult = str_L & "L"
fieldforeresult = str_E & "E"
fieldforsresult = str_S & "S"


It works perfectly for H, A and L.

But it does not work for E and S

When I change

fieldforhresult = str_H & "H"

to

fieldforhresult = str_E & "E"

It shows the correct result in the textbox on my form for the number of Es I have entered.

when i change

fieldforeresult = str_E & "E"

to

fieldforeresult = str_H & "H"

the box is empty

But when i change it back to

fieldforhresult = str_H & "H"
fieldforeresult = str_E & "E"

H = correct result
E= box empty

Any ideas what is wrong. I hope i have explained my problem properly.

Thanks
Arwyn

I don't know whether this is the only issue, but look at the following

str_E = Replace(str_E, "H", "")
str_E = Replace(str_E, "A", "")
str_E = Replace(str_E, "L", "")
str_E = Replace(str_E, "S", "")

str_S = Replace(str_Overall, "H", "")
str_S = Replace(str_S, "A", "")
str_S = Replace(str_S, "L", "")
str_S = Replace(str_S, "E", "")

For str_S, you've correctly started with str_Overall (the list of ALL letters).
For str_E, however, you've started with str_E itself. At that point, it will be set to be a blank string, so will contain no letters.

Matt

Thanks for showing me my error which I have now corrected but original problem still remains.

My code is now

Dim str_Overall As String

Dim str_H As String
Dim str_A As String
Dim str_L As String
Dim str_E As String
Dim str_S As String

Dim str_Final_H As Integer
Dim str_Final_A As Integer
Dim str_Final_L As Integer
Dim str_Final_E As Integer
Dim str_Final_S As Integer

str_Overall = [Jan2] & [Jan3] & [Jan4] & [Jan5]

str_H = Replace(str_Overall, "A", "")
str_H = Replace(str_H, "L", "")
str_H = Replace(str_H, "E", "")
str_H = Replace(str_H, "S", "")

str_A = Replace(str_Overall, "H", "")
str_A = Replace(str_A, "L", "")
str_A = Replace(str_A, "E", "")
str_A = Replace(str_A, "S", "")

str_L = Replace(str_Overall, "H", "")
str_L = Replace(str_L, "A", "")
str_L = Replace(str_L, "E", "")
str_L = Replace(str_L, "S", "")

str_E = Replace(str_Overall, "H", "")
str_E = Replace(str_E, "A", "")
str_E = Replace(str_E, "L", "")
str_E = Replace(str_E, "S", "")

str_S = Replace(str_Overall, "H", "")
str_S = Replace(str_S, "A", "")
str_S = Replace(str_S, "L", "")
str_S = Replace(str_S, "E", "")


str_H = Len(str_H)
str_A = Len(str_A)
str_L = Len(str_L)
str_E = Len(str_E)
str_S = Len(str_S)

fieldforhresult = str_H & "H"
fieldforaresult = str_A & "A"
fieldforlresult = str_L & "L"
fieldforeresult = str_E & "E"
fieldforsresult = str_S & "S"

End Sub

Thanks
Arwyn

str_Overall = [Jan2] & [Jan3] & [Jan4] & [Jan5]

msgbox str_Overall

str_H = Replace(str_Overall, "A", "")


If you add the line shown above and run the code, what gets displayed?
Are there any 'E's in the value displayed?

Hi Matt

After adding the code

MsgBox str_Overall

When I click my button, a box appears showing

HALE

which is the correct result.

Hope this helps
Arwyn

Okay, so we know the overall string is being produced correctly.
Process of elimination time: We add a few more Message Boxes and see at which point the code is going wrong.


Dim str_Overall As String

Dim str_H As String
Dim str_A As String
Dim str_L As String
Dim str_E As String
Dim str_S As String

Dim li_Final_H As Integer
Dim li_Final_A As Integer
Dim li_Final_L As Integer
Dim li_Final_E As Integer
Dim li_Final_S As Integer

str_Overall = [Jan2] & [Jan3] & [Jan4] & [Jan5]

str_H = Replace(str_Overall, "A", "")
str_H = Replace(str_H, "L", "")
str_H = Replace(str_H, "E", "")
str_H = Replace(str_H, "S", "")

str_A = Replace(str_Overall, "H", "")
str_A = Replace(str_A, "L", "")
str_A = Replace(str_A, "E", "")
str_A = Replace(str_A, "S", "")

str_L = Replace(str_Overall, "H", "")
str_L = Replace(str_L, "A", "")
str_L = Replace(str_L, "E", "")
str_L = Replace(str_L, "S", "")

msgbox "Point 0: " & str_Overall ' -- Hopefully says 'Point 0: HALE'
str_E = Replace(str_Overall, "H", "")
msgbox "Point 1: " & str_E ' -- Hopefully says 'Point 1: ALE'
str_E = Replace(str_E, "A", "")
msgbox "Point 2: " & str_E ' -- Hopefully says 'Point 2: LE'
str_E = Replace(str_E, "L", "")
msgbox "Point 3: " & str_E ' -- Hopefully says 'Point 3: E'
str_E = Replace(str_E, "S", "")
msgbox "Point 4: " & str_E ' -- Hopefully says 'Point 4: E'

str_S = Replace(str_Overall, "H", "")
str_S = Replace(str_S, "A", "")
str_S = Replace(str_S, "L", "")
str_S = Replace(str_S, "E", "")

li_H = Len(str_H)
li_A = Len(str_A)
li_L = Len(str_L)
li_E = Len(str_E)
msgbox "Point 5: Length = " & li_E ' -- Hopefully says 'Point 5: Length = 1'
li_S = Len(str_S)

fieldforhresult = li_H & "H"
fieldforaresult = li_A & "A"
fieldforlresult = li_L & "L"
fieldforeresult = li_E & "E"
msgbox "Point 6: FieldForEResult = " & me![fieldforeresult]
' -- Hopefully says 'Point 6: FieldForEResult = 1E'
fieldforsresult = li_S & "S"

End Sub

If all msgboxes display what we hope they will, then I'm a bit stuck for ideas.
If not, we should at least find out which part is going wrong.

Everything is correct but NOT the last one

Point 6: FieldforEResult=

there is nothing after =

Hope this helps

Arwyn

Nothing immediately springs to mind.

I have had something similar happen to me, when a field just would not allow me to update it. In that case there was something 'corrupt', for want of a better word, about the control itself. I was able to fix it by deleting the control from the form and recreating it by copying, pasting and renaming a working control.

I'll see if I can think of anything else, but in the meantime you may want to try the removal/recreation route.

Arwyn

this ought to be done with a function, so that you can test any letter. This version uses a const mystrg to test it, but calls a sub for every letter A - Z to count the instances of each letter

'start with a string that looks like this - spaces wont matter at all, so they don't need to be stripped out

Const mystrg = "HALESHHLESHHHLEEE" 'test string

Function parsechars(seekchar As String, returnstrg As String) As Boolean
'determine whether there are any hits in the string, and return a string with the count eg 5H, 4S

Dim hits As Long
Dim pos As Long
'return true if any hits
pos = 1 'start at the beginning of the string, then check the remainder of the string form the next char after the first hit
hits = 0
While InStr(pos, mystrg, seekchar) > 0
hits = hits + 1
pos = InStr(pos, mystrg, seekchar) + 1
Wend

'MsgBox ("Char: " & seekchar & " Returned: " & hits & " hits. ")
If hits > 0 Then returnstrg = str(hits) & seekchar
parsechars = hits > 0 'false if no hits, and the returnstring is ignored
End Function


Sub identifycounts()
Dim myint As Integer
Dim outstrg As String
Dim outch As String
Dim returnstrg As String

For myint = 1 To 26
'because access doesnt have a char datatype, have to force the test string to become A to Z in turn
outch = Chr(myint + 64)
If parsechars(outch, returnstrg) Then
outstrg = outstrg & returnstrg
End If
Next myint
MsgBox outstrg 'display the finished string

End Sub

Hi Gemma

I have got the code to work.

Matt was right when he said that my database might be corrupt.

I started again with a new database and entered the code below

Dim str_Overall As String

Dim str_H As String
Dim str_A As String
Dim str_L As String
Dim str_E As String
Dim str_S As String

Dim ll_final_H As Long
Dim ll_Final_A As Long
Dim ll_Final_L As Long
Dim ll_Final_E As Long
Dim ll_Final_S As Long

str_Overall = [jan2] & [jan3] & [jan4] & [jan5] & [jan8]

str_H = Replace(str_Overall, "A", "")
str_H = Replace(str_H, "L", "")
str_H = Replace(str_H, "E", "")
str_H = Replace(str_H, "S", "")

str_A = Replace(str_Overall, "H", "")
str_A = Replace(str_A, "L", "")
str_A = Replace(str_A, "E", "")
str_A = Replace(str_A, "S", "")

str_L = Replace(str_Overall, "H", "")
str_L = Replace(str_L, "A", "")
str_L = Replace(str_L, "E", "")
str_L = Replace(str_L, "S", "")

str_E = Replace(str_Overall, "H", "")
str_E = Replace(str_E, "A", "")
str_E = Replace(str_E, "L", "")
str_E = Replace(str_E, "S", "")

str_S = Replace(str_Overall, "H", "")
str_S = Replace(str_S, "A", "")
str_S = Replace(str_S, "L", "")
str_S = Replace(str_S, "E", "")

ll_final_H = Len(str_H)
ll_Final_A = Len(str_A)
ll_Final_L = Len(str_L)
ll_Final_E = Len(str_E)
ll_Final_S = Len(str_S)

fieldforhresult = Str(ll_final_H) & "H"
fieldforaresult = Str(ll_Final_A) & "A"
fieldforlresult = Str(ll_Final_L) & "L"
fieldforeresult = Str(ll_Final_E) & "E"
fieldforsresult = Str(ll_Final_S) & "S"

It works perfectly in my new database but when I copy it to my old database it does not work.

Thanks for the help

Arwyn

When you said in your last message that my database might be corrupt, I started again with a new database. I changed the code to

Dim str_Overall As String

Dim str_H As String
Dim str_A As String
Dim str_L As String
Dim str_E As String
Dim str_S As String

Dim ll_final_H As Long
Dim ll_Final_A As Long
Dim ll_Final_L As Long
Dim ll_Final_E As Long
Dim ll_Final_S As Long

str_Overall = [jan2] & [jan3] & [jan4] & [jan5] & [jan8]

str_H = Replace(str_Overall, "A", "")
str_H = Replace(str_H, "L", "")
str_H = Replace(str_H, "E", "")
str_H = Replace(str_H, "S", "")

str_A = Replace(str_Overall, "H", "")
str_A = Replace(str_A, "L", "")
str_A = Replace(str_A, "E", "")
str_A = Replace(str_A, "S", "")

str_L = Replace(str_Overall, "H", "")
str_L = Replace(str_L, "A", "")
str_L = Replace(str_L, "E", "")
str_L = Replace(str_L, "S", "")

str_E = Replace(str_Overall, "H", "")
str_E = Replace(str_E, "A", "")
str_E = Replace(str_E, "L", "")
str_E = Replace(str_E, "S", "")

str_S = Replace(str_Overall, "H", "")
str_S = Replace(str_S, "A", "")
str_S = Replace(str_S, "L", "")
str_S = Replace(str_S, "E", "")

ll_final_H = Len(str_H)
ll_Final_A = Len(str_A)
ll_Final_L = Len(str_L)
ll_Final_E = Len(str_E)
ll_Final_S = Len(str_S)

fieldforhresult = Str(ll_final_H) & "H"
fieldforaresult = Str(ll_Final_A) & "A"
fieldforlresult = Str(ll_Final_L) & "L"
fieldforeresult = Str(ll_Final_E) & "E"
fieldforsresult = Str(ll_Final_S) & "S"

It works perfectly in my new database, but when I copy it to my old database
it fails to work. You were right about the corrupt file.

Thank you very much for your time and effort.

I shall be watching Wales v Canada on Saturday.

Thank you
Arwyn

*phew* Glad that one finally got sorted for you. :D

If you get the time to do so, Gemma was definitely right about creating a function. I didn't mention it before, as the initial problem only involved a few letters, so it didn't seem worthwhile. Now that - theoretically, at least - you could end up using all 26, it would make life easier for you to be able to call one piece of code that does all the work for each letter, rather than needing to add on new parts to handle each new letter.

The game's on at 2:30 local time, here, so I'll have to see if it's on one of the channels we get, as opposed to needing to buy a sports package. I did manage to see the devastation we inflicted on them and the US last Summer.

Please note the game is on FRIDAY NIGHT UK time with a 7pm kick off.

The television programe starts at 7pm so kick could be a little later.

Please let me know you have recieved this message

Thanks
Arwyn

Received, thanks.

Either I got the day wrong, or it's being repeated here on the Saturday.