Multiple Criteria – Conditional Formatting (Using Access 97 SR2 )

Topic

New Reply

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.

My problems: When I use multiple criteria in the OnFormat command, I can only get one of them to work, and I can’t seem to get wildcards to work.

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 and need to build criteria for.

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 )

Any suggestions will be greatly appreciated !!

Bob

Reply | Quote

Replies

Your line

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

is not valid in VBA:
– You shouldn’t repeat If.
– For wildcards, you shold use Like.
– And has precedence over Or unless you use brackets.

It should be

If Me![HourTypeCode] Like “L*” And (Me![WC4] Not Like “GL*” Or Me![WC5] Not Like “GL*”) Then
or
If Left(Me![HourTypeCode], 1) = “L” And (Left(Me![WC4], 2) “GL” Or Left(Me![WC5], 2) “GL”) Then

If you have multiple conditions, you can use If … Then … ElseIf … Then … ElseIf … Then … Else … End If (as many ElseIf’s as you want). For many different values of a single variable, you can use Select Case:

Select Case Left(Me![HourTypeCode], 1)
Case “A”
…
Case “L”
…
Case “N”
…
Case Else
…
End Select

To check the last 4 characters, you can use the Right function:

If Right(Me![WC2], 4) “FY03” Then

I hope this gives you some idea of what you can do. Post back if you need more assistance.

Reply | Quote

Hello Hans,

Yes, you’ve provided some guidance. Could you please post an example of how I could use the CASE statement? It appears more flexible. Thanks. I’m at work now, and will try the other examples you’ve shown.

Bob in Indy

Reply | Quote

The online help for Access 97 has an example of the use of Select Case.

If you create a switchboard using the Switchboard Manager (in Tools/Add-Ins in Access 97), the HandleButtonClick routine in the code behind the Switchboard form has a nice example of using Select Case to execute various actions depending on the value of a field.

Reply | Quote

Hello again,

Here is what happens when I tried these examples….

If Me![HourTypeCode] Like “L*” And(Me![WC4] Not Like “GL*” Or Me![WC5] Not Like “GL*”) Then
*** This line yields a COMPILE Error

If Left(Me![HourTypeCode], 1) = “L” And (Left(Me![WC4], 2) “GL” Or Left(Me![WC5], 2) “GL” Then
*** The above line returns: Compile Error: Expected )

If Left(Me![HourTypeCode], 1) = “L” And (Left(Me![WC4], 2) “GL” Or Left(Me![WC5], 2)) “GL” Then
*** I tried adding an extra ). This line yields a Run Time Error ’13’ Type Mismatch.

I’m still playing with it, but with little success.

Bob

Reply | Quote

In the first example, you omitted a space between And and (Me …

In the second and third example, you didn’t put the closing bracket as in my example, near the end of the line:

If Left(Me![HourTypeCode], 1) = “L” And (Left(Me![WC4], 2) “GL” Or Left(Me![WC5], 2) “GL”) Then

Reply | Quote