Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Criteria - Conditional Formatting (Using Access 97 SR2 )

    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 <smile> )

    Any suggestions will be greatly appreciated !!

    Bob

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple Criteria - Conditional Formatting (Using Access 97 SR2 )

    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.

  3. #3
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Criteria - Conditional Formatting (Using Access 97 SR2 )

    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

  4. #4
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Criteria - Conditional Formatting (Using Access 97 SR2 )

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple Criteria - Conditional Formatting (Using Access 97 SR2 )

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple Criteria - Conditional Formatting (Using Access 97 SR2 )

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •