Text

[Matt Greatorex]

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

 

[Arwyn]

Problems again

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

 

[Matt Greatorex]

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.

 

[Arwyn]

My Code

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

 

[Matt Greatorex]

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?

 

[Arwyn]

Result from your last code

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

 

[Matt Greatorex]

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.

 

[Arwyn]

Your last code

Everything is correct but NOT the last one

Point 6: FieldforEResult=

there is nothing after =

Hope this helps

Arwyn

 

[Matt Greatorex]

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.

 

[gemma-the-husky]

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

 

[Arwyn]

It Works

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

 

[Arwyn]

You were right

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

 

[Matt Greatorex]

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

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.

 

[Arwyn]

Wales v Canada

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

 

[Matt Greatorex]

Received, thanks.

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