# Need format for triples in 4 IN FIELD WITH MS ACCESS 2013

have an table_A with field4

field have  an four letters  like ABCD
need to format when triple letters happens!

like  AAAB,    BBBC   AABA  BAAA  ABAA  AABA
i mean the triple can occur in any order
need to conditional formating  when happens

###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Infotrakker SoftwareCommented:
What have you tried so far?

EE isn't really a place where you post requests and have someone else do this for you. You're expected to do the work, and we'll help you when you get stuck.
Author Commented:
i try some expression but still loss!
i know i need some direction my good friend!
Author Commented:
i try it but still loss!
IIf(Mid([FieldName],1,1)=Mid([FieldName],2,1),-1,
IIf(Mid([FieldName],1,1)=Mid([FieldName],3,1),-1,
IIf(Mid([FieldName],2,1)=Mid([FieldName],3,1),-1,0)))=-1
Owner, Developing Solutions LLCCommented:
You are not going to be able to create a single condition that identifies triplets like that.  You could, but it would be extremely long and confusing to someone.  You are going to need to write a function, pass it the value, and return either a True (-1) or False (0) value back from the function to the conditional formatting.  You can then use that function as an expression in the conditional formatting for the field.

My recommendation for your function would be to identify the 1st character using the left or mid function.  Then use the Len function in conjunction with the Replace function to determine whether 3 or more characters are the same.  something like:

strChar = Mid(PassedValue, 1, 1)
if Len(Replace(PassedValue, strChar, "")) <= 1 then
return true to function and exit function

If the 1st character doesn't work, then perform the same test for the 2nd character.  if you are looking for a triplet, then either the 1st character or the 2nd would have to match two other positions.  If neither the 1st or 2nd matches 2 others, then return False via the function.

Then in the Expression for conditional formatting, you should be able to do something like:

fnContainsTriplet([yourFieldName]) = -1

as the expression and set your color or formatting based on that.
Author Commented:
im triying that approach but still loss!
Owner, Developing Solutions LLCCommented:
What have you tried?
Author Commented:
IIf(Mid([FieldName],1,1)=Mid([FieldName],2,1),-1,
IIf(Mid([FieldName],1,1)=Mid([FieldName],3,1),-1,
IIf(Mid([FieldName],2,1)=Mid([FieldName],3,1),-1,0)))=-1
Owner, Developing Solutions LLCCommented:
No, as I stated in my original post, trying to write a critiera that way will be virtually unreadable;  create a function.

Pass the function the value of your field, whatever that is.  I gave you some sample code logic (not really code although part of it was).  I've got a working sample, but you should try my recommendation first.  Provide your sample function using the logic in my previous post and I'll work with you on this.
Author Commented:
how can i do that i was try the expression because is more
esay by steps
Owner, Developing Solutions LLCCommented:
OK, so are you saying you do not know how to create a function?

In Access, press Ctrl-G to open the VBA window

If you right click in the Project window (usually in the upper left corner, but can be place anywhere), you will see a popup menu which has an Insert option, expand that and select Module.
If you then expand the Modules node of the treeview, there will be a new module located at the bottom of that node named something like Module1. Double click on that module to open the code window for that module.

At the top of the module, you should see:
Option Compare Database
Option Explicit
If that is not there, put it there.  Then create a new function:
Public Function fnContainsTriplet(SomeValue as String) as boolean

fnContainsTriplet = True

End Function
When you have that, then go to the form where you want to implement conditional formatting, select the control that you want to format, click the conditional formatting button on the ribbon, change the "Condition 1" from "Field Value Is" to "Expression Is" and then enter the example as shown below (changing "textField" to the name of the field that is bound to this control).
Now, when you open your form, all of the records should be color coded blue.  This is because the function we created only has one line of code, which returns True for every record.  Close your form, go back to the function and change the code to return False for every record.

When you have that working, or cannot get it working, let me know.
Author Commented:
I DID !
Commented:
Pass one of the four character strings into this function.
Public Function ContainsTriple(ByVal parmString) As Boolean
Static oRE As Object
If oRE Is Nothing Then
Set oRE = CreateObject("vbscript.regexp")
oRE.Pattern = "(\w)\1\1"
End If
If IsNull(parmString) Then
ContainsTriple = False
Else
ContainsTriple = oRE.test(parmString)
End If
End Function
Author Commented:
I get lost here ! in this point

also i have to say there is any letter
not just ABCD  could be  G X W Y T S all the alphabet
what  i need to track is when any letter repeat 3 times in any sequence
Owner, Developing Solutions LLCCommented:
Try replacing your function with this.  It will first check and make sure that something was passed to the function, then confirm that the string has 4 characters.  If neither of those works, it send False back from the function.

Then it checks the 1st and then the 2nd characters to determine whether 3 or more of the characters match that value and if so, returns True to the function, otherwise, it will return False.

Public Function fnContainsTriplet(SomeValue As Variant) As Boolean

Dim strChar As String

If IsNull(SomeValue) Then
fnContainsTriplet = False
Exit Function
ElseIf Len(SomeValue) <> 4 Then
fnContainsTriplet = False
Exit Function
End If

strChar = Mid(SomeValue, 1, 1)
If Len(Replace(SomeValue, strChar, "")) <= 1 Then
fnContainsTriplet = True
Exit Function
End If

strChar = Mid(SomeValue, 2, 1)
If Len(Replace(SomeValue, strChar, "")) <= 1 Then
fnContainsTriplet = True
Exit Function
End If

End Function
Owner, Developing Solutions LLCCommented:
@aikimark,

One of these days, I'm going to spend some time trying to understand Regular Expressions.  Right now, that looks like Greek to me!

Dale
Commented:
@Dale

Just try my function.
Author Commented:
please can tell me  what to do now !  very sorry but
you say
replece it ?
fnContainsTriplet = True

with the last code you send me ?
Commented:
\w = any A-Z, a-z, 0-9, or _ character
\1 = whatever matched inside the preceding parenthesis expression
Owner, Developing Solutions LLCCommented:
@Joan,

Yes, copy everything after the line starting : Public Function
and prior to the line: End Function

Then paste that in the function you created earlier, replacing the line that we did during the earlier test.
Owner, Developing Solutions LLCCommented:
@aikimark,

You missed the option that the "triplet" is not necessarily in consecutive positions, it just have to have 3 of the same characters, in any position.  Your function currently fails with "AABA".

I'm sure you can figure out the alternatives for "ABAA" and "AABA"
Commented:
I misunderstood what "triple letters" meant.
Commented:
The correct regex pattern is this:
(\w).*\1.*\1
Owner, Developing Solutions LLCCommented:
Yep, that did it.  Like I said, one of these days I will sit down and study Regular Expressions so that I can at least understand what I'm reading.

;-)
Dale
Commented:
@Dale

This pattern will also work on longer strings, not just strings of length 4
Commented:
@joan
if you are having a hard time to follow the VBA codes, just try to use this, copy and paste

Expression Is  IIf(Mid([field4],1,1)=Mid([field4],2,1) And Mid([field4],1,1)=Mid([field4],3,1),-1,IIf(Mid([field4],1,1)=Mid([field4],2,1) And Mid([field4],1,1)=Mid([field4],4,1),-1,IIf(Mid([field4],1,1)=Mid([field4],3,1) And Mid([field4],1,1)=Mid([field4],4,1),-1,IIf(Mid([field4],2,1)=Mid([field4],3,1) And Mid([field4],2,1)=Mid([field4],4,1),-1,0))))=-1

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
You can also do some clever bit masking.  This detects three or four characters the same.
x="AABA"
?cbool(sgn(asc(mid(x,1,1)) xor asc(mid(x,2,1))) xor sgn(asc(mid(x,3,1)) xor asc(mid(x,4,1))) _
Or ((asc(mid(x,1,1)) and asc(mid(x,2,1)) and asc(mid(x,3,1)) and asc(mid(x,4,1))) = asc(mid(x,1,1))))
Author Commented:
GENTLEMEN ! ALL OF YOU ARE THE BEST!
thanks for you time and consideration with me
I learn a lot with your post solutions
feel in goods hands!

Scott McDaniel (Microsoft Access MVP - EE MVE )  thnks for you suggestions

Dale Fye (Access MVP) you are great for you time and solutions make me think a new way

aikimark you too! show me a new way

Rey Obrero (capricorn1)  you lead the way!
Author Commented: