Conditional formatting on a field...only looks like it handles numbers instead of functions or field 1

[Pack10]

I have a split form that I use as a grid, where the user selects a record and clicks on a field to edit the record. The records are colored based on how long they have been active. So up to three days the "Time on" column is Green, 5-8 days yellow and greater than 8 Red using the conditional formatting.
However, we would like to make the days parameter more assignable and not a hard coded value. So, on the record I added three fields Low, Med, and High and assign a value. However, in the conditional format wizard, its does not seem to recognize the value (high, med, low) on the record for the calculation to determine the color. No color is selected, it appears to only handle a real value like 3 and 5 in a greater than less than construct.
HAs anyone had success using functions (User defined) that return a value or a field in the record for the calculation. Should work....

 

[dhookom]

What is the expression that you attempted to use?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Pack10 . . .

You havn't mentioned which field [blue](should be a date field)[/blue] is used as a reference for calculations! Could you at least post the three formats as you have them now?

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[TheAceMan1]

Pack10 . . .

If I go by your post origination and assuming you have a datefield named refDate ... I come-up with the following for yellow! ...

[blue]Expression Is ([refDate]>[refDate]+5) AND ([refDate]<[refDate]+8)[/blue]

See how easy! ... although I can't see the need for such a wildly colored thing.

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[Pack10]

The field I am using in the split form that I want to color based on a condition is a calculated field. The control source for the field is an If condition.
It is tracking a task, if the task has a complete date, it calculates business days by passing the received date and the complete date, if its still in progress, it passes the start date and Now(). Its a "Time On" field. Initially I based the Conditional Formatting with a statement color GREEN if < 3 days, Yellow if between 4 and 7 and red if greater than or equal to 8. However, this app tracks tasks and projects, so we want to base the color formatting on the task or project by storing the Green, Yellow and red value in the record so tasks and projects can be measured differently.
Anyway, I am using Expression Is and then wrote a function to return the field that contains the SC_Low, SC_Mod, and SC_High value instead of a hard coded value. It seems the Expression Is cant handle a function...I am using Access 2007.

 

[TheAceMan1]

Pack10 . . .

[blue]dhookom[/blue] and I need to see the [blue]expression[/blue] and the [blue]function![/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[Pack10]

The usage is ....
Condition 1
Field Value Is less than or equal to Get_SC_Low([Parent])

Condition 2
Field Value Is Between than or equal to Get_SC_Low([Parent]) And Get_SC_Mod([Parent])

Condition 3
Field value is greater than Get_SC_High([Parent])

Please Note: I have tried this with field value and Expression is....if I hard code a numeric value instead of the function it works...



Here are the functions.... there is one for low, one for moderate and one for high

Function Get_SC_Low(holdParent As Integer) As Integer

Dim iCriteria As Integer
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(Name:="Assigned_DDQs", Type:=dbOpenDynaset)
rs.FindFirst "[Parent] = " & holdParent

If Not rs.NoMatch Then

With rs
Get_SC_Low = .Fields("SC_Low")
End With

End If

End Function

Function Get_SC_Mod(holdParent As Integer) As Integer

Dim iCriteria As Integer
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(Name:="Assigned_DDQs", Type:=dbOpenDynaset)
rs.FindFirst "[Parent] = " & holdParent

If Not rs.NoMatch Then

With rs
Get_SC_Mod = .Fields("SC_Moderate")
End With

End If

 

[dhookom]

How do you select Condition 2: "Field Value Is Between than or equal to Get_SC_Low([Parent]) And Get_SC_Mod([Parent])"

I would also use only a single function with L or M sent in as an argument to determine which field to return:

Function Get_SC(holdParent As Integer, strLM As String) As Integer
    [COLOR=#4E9A06]'strLM is either L or M for SC_Low or SC_Moderate[/color]
    Dim iCriteria As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(Name:="Assigned_DDQs", Type:=dbOpenDynaset)
    rs.FindFirst "[Parent] = " & holdParent
    If Not rs.NoMatch Then
        With rs
            Select Case strLM
                Case "L"
                    Get_SC = .Fields("SC_Low")
                Case "M"
                    Get_SC = .Fields("SC_Moderate")
            End Select
        End With
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Function

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Pack10 . . .

You forgot to post your [blue]Get_SC_High[/blue] function. which should convert that provided by [blue]dhookom[/blue] to:

[blue]elect Case strLM
   Case "L"
      Get_SC = .Fields("SC_Low")
   Case "M"
      Get_SC = .Fields("SC_Moderate")
   Case "[purple][b]H[/b][/purple]"
      Get_SC = .Fields("SC_[purple][b]High[/b][/purple]")
End Select[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[dhookom]

You would call the single function like:

Get_SC([Parent],"L")
Get_SC([Parent],"M")
Get_SC([Parent],"H")

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

Hi

Thanks for responding, should I still use the Conditional Formatting interface which contains the three conditions....
or am i better off using code..because when using the interface, the function does not seem to be invoked.

 

[MajP]

if you use code to set the formatconditions that should be identical to doing it at design time. That fact that it is not working is a logic or coding error not a general issue with conditional formatting If you are asking about changing the colors of a field by code, that will only work in single form view. In continuous view they would all change

 

[dhookom]

Have you ever tested the function(s) to make sure they are returning the expected values?

Did you change your function? Did you add the code to close the recordset and set objects to Nothing?

Have you considered calculating the value in your form's record source data rather than in the conditional formatting?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Pack10 . . .

Following are the three conditional formats you currently have:

[blue]Condition 1
Field Value Is less than or equal to [purple]Get_SC_Low[/purple]([Parent])

Condition 2
Field Value Is Between [purple]Get_SC_Low[/purple]([Parent]) And [purple]Get_SC_Mod[/purple]([Parent])

Condition 3
Field value is greater than [purple]Get_SC_High[/purple]([Parent])[/blue]

Go ahead ... try it! ... or modify it ... something should happen! ...


[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[Pack10]

Will try when I get in...The functions work when running them from debug menu.

 

[Pack10]

Thanks to all especially Duane...got it to work.