asked on

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?

DBDevl

Try:

Like "*" & fieldName1 & "*"

or

Like "*'" & fieldName1 & "'*"

Karen Schaefer

ASKER

sorry neither way works - getting a syntax error. see attached.co
ConditionalFormatPic.jpg

DBDevl

Try this:

In the Condition 1 drop down select Expression Is, then enter this, InStr([field1].Value,[field2].value)>0

DBDevl

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

InStr([field1].Value,[field2].value, 1)>0

DBDevl

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.

Karen Schaefer

ASKER

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

DBDevl

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)

DBDevl

So, conversely, if any of those values are in Eqpt_Loc_No it will return false.

Karen Schaefer

ASKER

thanks for the assist, however problem with syntax.

K

ASKER CERTIFIED SOLUTION