Wildcard conditional format

[rpadams]

I have a report that has 2 fields of interest. One is [AmtChk] and the other is [CheckNum] (an alpha text field). What I want to do is set a condition based on the first letter in [CheckNum] that changes the color of the [AmtChk]. This is used to flag different accounts.

For example if a [CheckNum] is A93 then [AmtChk] will be in red, a B90 might be unformatted.

I've set the expression to [CheckNum]="A"&"*" but it doesn't work. [CheckNum]="A93" works but only for this explicit example.

I eventually got it working the [CheckNum] Between "A" and "B" but this isn't really the wildcard I wanted.

 

[Mr. B]

I prefer to handle things like this using a user defined function. So try adding the following function at the beginning of your form's module.

Function FormatAmt(FirstChar As String)
Select Case FirstChar
Case "A"
'set the forecolor of the Amount to Red
Me.NameOfAmtChkControl.ForeColor = 255
Case "B"
'set the forecolor of the Amount to Blue
Me.NameOfAmtChkControl.ForeColor = 16711680
Case "C"
'set the forecolor of the Amount to Green
Me.NameOfAmtChkControl.ForeColor = 32768
Case "D"
'set the forecolor of the Amount to Black
Me.NameOfAmtChkControl.ForeColor = 0
End Select
End Function

This function is where the action is all defined. The function uses the character (FirstChar) passed to it to determine which color is applied to the AmtChk control.

To call the function just use the following in the AfterUpdate event of your CheckNum control. You should also place this same statement in the On Current event of your form.

FormatAmt Left(Me.NameOfCheckNumControl, 1)
If Not IsNull(Me.NameOfCheckNumControl) Then
If Not IsNumeric(Left(Me.NameOfCheckNumControl, 1)) Then
FormatAmt Left(Me.NameOfCheckNumControl, 1)
End If
Else
Me.NameOfAmtChkControl.ForeColor = 0
End If

Just change the "NameOfCheckNumControl" to the actual name of your check number control and the "NameOfAmtChkControl" to the actual name of your Amount control everwhere in the code . Please note that this code will also check for and handle a check number that does not begin with an alpha character. If you are already checking for this, then you could just do away with that checking in the code.

HTH