Conditional Formatting of a Text Field containing characters A-Z

[JohanG]

Greetings Experts,



I want to highlight a field [ProductCode] defined as Text on a listed sub form if any of the listed ProductCode values contain any alpha characters or are not 6 characters in length.

Our "ProductCodes" are typically numerical like values such as 09876 saved in said Text field; I want to highlight any fields that do not conform to 6 numerical values.

For Example:

If the value is something like "R34561" (contains an alpha character) or "678" (less than 6 characters) or "abc4321" (contain multiple alpha characters and/or contain more than 6 characters) or "6321452" (more than 5 characters/numerals) it must be highlighted.

If the value contains any numerical digit 0-9 and Length = 6 then the field must NOT be highlighted (e.g. 654328)

I have tried adding the conditional formatting expression:

If Like "*[A-Za-z]*" Or Len([ProductCode])<>6

But using the above also formats valid Product Codes such as "654328" (which it shouldn't)



Not sure where I'm missing the plot...Please assist?

Much appreciated!

[JohanG]

I have managed to accuire the correct results by adding the following expression:

(Asc([ProductCode])>64 And Asc([ProductCode])<91) Or Len([ProductCode])<>6

[Bob Fitz]

I have managed to accuire the correct results by adding the following expression:

(Asc([ProductCode])>64 And Asc([ProductCode])<91) Or Len([ProductCode])<>6

Keep in mind that it won't catch letters "a-z" though

[JohanG]

So I should probably amend the expression to: (Asc(UCase([ProductCode]))>64 and Asc(UCase([ProductCode]))<91) or Len([ProductCode])<>6

Do you agree?

[Bob Fitz]

I think you might need the following but I haven't tested it:

((Asc([ProductCode])>64 And Asc([ProductCode])<91) OR (Asc([ProductCode])>97 And Asc([ProductCode])<122)) Or Len([ProductCode])<>6

[June7]

Maybe:

Not IsNumeric([ProductCode]) Or Len([ProductCode])<>6

or

IsNumeric([ProductCode]) = False Or Len([ProductCode])<>6

[JohanG]

Hi June7,

IsNumeric([ProductCode]) = False Or Len([ProductCode])<>6

Works perfectly :-)