Multiple Criteria - Conditional Formatting (1 Viewer)

[indyaries]

Good Morning,


Using Access 97 SR2

I've been tasked to determine user errors on a new timekeeping system (eBiz) that we are using. In one payperiod there are over 1600 records. I've searched the REPORT threads for using multiple criteria in Conditional Formatting, but have not seen anything that would help.

The fields that I'm analyzing are: (WC = WorkCode)
HourTypeCode, WC1, WC2, WC3, WC4, WC5

Example 1:
An employee enters LA (Leave-Annual) as HourTypeCode
WC4 should begin with GL (GL000)
WC5 should begin with GL (GL0007)

In the above example, I want a report that will highlight the three field in RED font and place a border around the field IF HourTypeCode begins with L and if EITHER WC4 or WC5 do not begin with GL. Something like this:

If Me![HourTypeCode] = "L*" And if Me![WC4] Not "GL*" Or If Me![WC5] Not "GL*" Then
Me![HourTypeCode].ForeColor = vbRed
Me![HourTypeCode].Border = 1
Me![WC4].ForeColor = vbRed
Me![WC4].Border = 1
Me![WC5].ForeColor = vbRed
Me![WC5].Border = 1
Else
Me![HourTypeCode].ForeColor = vbBlack
Me![HourTypeCode].Border = 0
Me![WC4].ForeColor = vbBlack
Me![WC4].Border = 0
Me![WC5].ForeColor = vbBlack
Me![WC5].Border = 0

Hence, if HourTypeCode begins with L, and if either WC4 OR WC5 do not begin with GL then I want the report to format the appropriate fields as I've shown above. Note that even if HourTypeCode begins with L, WC4 could begin with GL, yet WC5 could have AB.

There are a multitude of criteria that I've identified. Just wanting a report that details all of the error types I've identified.

Another example is WC2. It must end in FY03. It is a long string of text and numbers, but should end in FY03. Some employees are using FY02. If they do, I would want WC2 highlighted as above.

Every field has potential errors in it. Some employees are authorized to use a special code in WC3 that others are using, but not authorized to use.

This report will be run every pay period, looking for user timekeeping errors. And, before you ask...NOPE!! The timekeeping software does NOT have built-in edits to catch these errors at the time of input. Something that someone shoved down the Governments throat.

I can supply a sample DB if needed. I would probably send it in Excel 97 so that I could have the fields that have errors formatted as I would like them to be in Access, to illustrate the complexity of this problem (complex for me, anyway <smile>)

Any suggestions will be greatly appreciated !!

Bob

 

[dynamictiger]

I like using VBA in reports. However, one of the things I have never quite worked out is why when you use statements like:

If Me![HourTypeCode] = "L*" And if Me![WC4] Not "GL*" Or If Me![WC5] Not "GL*" Then

It never seems to work properly. I know it should and I know that the books say it should, however, try as I might they just don't.

I think you would be better served writing this like:

if left(Me.HourTypeCode,1)="L" then

If Not Left(Me.WC4,1)="GL" then

Or whatever. I am assuming in the above the field values start with L or GL you could redirect this to the controlsource or what have you.