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

any help please!
joan riverAsked:
Who is Participating?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )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.
0
joan riverAuthor Commented:
i try some expression but still loss!
i know i need some direction my good friend!
0
joan riverAuthor 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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
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.
0
joan riverAuthor Commented:
im triying that approach but still loss!
0
Dale FyeCommented:
What have you tried?
0
joan riverAuthor 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
0
Dale FyeCommented:
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.sample output
0
joan riverAuthor Commented:
how can i do that i was try the expression because is more
esay by steps
0
Dale FyeCommented:
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.insert 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

Open in new window

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

Open in new window

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).conditional formatting.
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.
0
joan riverAuthor Commented:
I DID !
0
aikimarkCommented:
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

Open in new window

0
joan riverAuthor 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
0
Dale FyeCommented:
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

Open in new window

0
Dale FyeCommented:
@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
0
aikimarkCommented:
@Dale

Just try my function.
0
joan riverAuthor 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 ?
0
aikimarkCommented:
\w = any A-Z, a-z, 0-9, or _ character
\1 = whatever matched inside the preceding parenthesis expression
0
Dale FyeCommented:
@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.
0
Dale FyeCommented:
@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"
0
aikimarkCommented:
I misunderstood what "triple letters" meant.
0
aikimarkCommented:
The correct regex pattern is this:
(\w).*\1.*\1

Open in new window

0
Dale FyeCommented:
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
0
aikimarkCommented:
@Dale

This pattern will also work on longer strings, not just strings of length 4
0
Rey Obrero (Capricorn1)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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
aikimarkCommented:
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))))

Open in new window

0
joan riverAuthor 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!
0
joan riverAuthor Commented:
Terrific ! you lead !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.