Conditional formatting Datasheet subform

How do I set the conditional formatting of a field where it looks at the value of another field and using a wild card determine if the value of the current field is contained within the value of the 2nd field?

I tried to set the conditional formatting to using the Expression not equal to Like "* fieldName1 *" or Like "*& fieldName1 & *"  I even tried to use a variable function that sets the value of Field2 within the Like statement without success.  

what is the proper syntax?
Karen SchaeferBI ANALYSTAsked:
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.

DBDevlCommented:
Try:

Like "*" & fieldName1 & "*"

or

Like "*'" & fieldName1 & "'*"
0
Karen SchaeferBI ANALYSTAuthor Commented:
sorry neither way works - getting a syntax error.  see attached.co
ConditionalFormatPic.jpg
0
DBDevlCommented:
Try this:

In the Condition 1 drop down select Expression Is, then enter this, InStr([field1].Value,[field2].value)>0
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DBDevlCommented:
Put a 1 as the last parameter to make the search not case sensitive:

InStr([field1].Value,[field2].value, 1)>0
0
DBDevlCommented:
Here is a complete description, it seems it may work for what you are trying to do.

InStr([start,] string1, string2 [, compare])

The InStr function syntax has these parts:

start Optional. Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. The start argument is required if compare is specified.

string1 Required. String expression being searched.

string2 Required. String expression sought.

compare Optional; numeric. A value of 0 (the default) specifies a binary (case-sensitive) search. A value of 1 specifies a textual (case-insensitive) search.
0
Karen SchaeferBI ANALYSTAuthor Commented:
sorry a little confused - When I use the InStr([Eqpt_Loc_N0].Value,[txtApNO].Value)>0, doesn't this mean if the 2 fields = each other than change the format.

I need to check if not equal to each other and also some other criteria exists.

1.  where Eqpt_Loc_N0<> txtapno
2.  Where Eqpt_Loc_N0] <> LIKE "*LH*' OR LIKE "*LAB*"

So what is the proper syntax to handle the various options?

Thanks
k
0
DBDevlCommented:
This should return true if txtApNo, LH, LAB are not in Eqpt_Loc_No, but I haven't tested it.

(InStr([Eqpt_Loc_N0].Value,[txtApNO].Value,1)=0) And (InStr([Eqpt_Loc_N0].Value"LH",1)=0) And (InStr([Eqpt_Loc_N0].Value,"LAB",1)=0)
0
DBDevlCommented:
So, conversely, if any of those values are in Eqpt_Loc_No it will return false.
0
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for the assist, however problem with syntax.

K
0
Jeffrey CoachmanMIS LiasonCommented:
kfschaefer1,

A sample would go a long way to avoiding any abiguity...

For eaxmple instead of saying:
" formatting of a field where it looks at the value of another field"
Huh?

You instead say:
I have a LastName Field on the subform, If the first 3 characters of the Last Name on the subform matches with the first 3 characters of the LastName on the Main form then make the Country Field on the subform Red.

c'mon k, how long have you known me....

;-)

Here is a sample, ...in "Real words" here is what it does.
It formats the ShipCountry in the Subform to Red if the Main form's CompanyName contains "er"  (Like "*er*")

I am sure you can take this sample and, on your own, easily adapt it for almost any combination of conditions you need.

Jeff
Access-EEQConditionallyFormatDat.mdb
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
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks Jeff, for your input as always you are a great help.
0
Jeffrey CoachmanMIS LiasonCommented:
I'm trying to get you to "then next Level"

;-)

Jeff
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.