Coding for future month results

[John]

Below is my current code for October's reporting. It is a project form
showing planned progress (OctPlan), actual progress (OctAct) and via the
attached code, an indicator and color for October (OctInd). This code works
great (thanks to the help on here) However, my form shows all 12 months, so
the code currently causes future months to indicate red. Is there a an If
then type statement that I can place this current code into that will
basically say If OctAct is null then OctInd = N/A? or is null ruined based
on the the code? Is there some other procedure I can try? I've tried using
dates to no avail. This may be due to my lack syntax knowledge.

Private Sub PEPOctAct_AfterUpdate()
On Error Resume Next
If Me.PEPOctAct > 0.0001 Then
Me.PEPOctAct = Me.PEPOctAct / 100
End If
Dim dblPercentage As Double
dblPercentage = (Me.PEPOctAct.Value / Me.PEPOctPlan.Value) * 100
Select Case dblPercentage
Case 80 To 95
Me.PEPOctInd.Value = "Y"
Me.PEPOctInd.BackColor = vbYellow
Case Is < 80
Me.PEPOctInd.Value = "R"
Me.PEPOctInd.BackColor = vbRed
Case Is >= 95
Me.PEPOctInd.Value = "G"
Me.PEPOctInd.BackColor = vbGreen
End Select
If Me.PEPOctPlan < 0.0001 Then
Me.PEPOctInd.Value = "N/A"
End If

 

[Marshall Barton]

Below is my current code for October's reporting. It is a project form
showing planned progress (OctPlan), actual progress (OctAct) and via the
attached code, an indicator and color for October (OctInd). This code works
great (thanks to the help on here) However, my form shows all 12 months, so
the code currently causes future months to indicate red. Is there a an If
then type statement that I can place this current code into that will
basically say If OctAct is null then OctInd = N/A? or is null ruined based
on the the code? Is there some other procedure I can try? I've tried using
dates to no avail. This may be due to my lack syntax knowledge.

Private Sub PEPOctAct_AfterUpdate()
On Error Resume Next
If Me.PEPOctAct > 0.0001 Then
Me.PEPOctAct = Me.PEPOctAct / 100
End If
Dim dblPercentage As Double
dblPercentage = (Me.PEPOctAct.Value / Me.PEPOctPlan.Value) * 100
Select Case dblPercentage
Case 80 To 95
Me.PEPOctInd.Value = "Y"
Me.PEPOctInd.BackColor = vbYellow
Case Is < 80
Me.PEPOctInd.Value = "R"
Me.PEPOctInd.BackColor = vbRed
Case Is >= 95
Me.PEPOctInd.Value = "G"
Me.PEPOctInd.BackColor = vbGreen
End Select
If Me.PEPOctPlan < 0.0001 Then
Me.PEPOctInd.Value = "N/A"
End If

If PEPOctAct is null, then you will get an error because of
the attempt to put Null into dblPercentage.

Perhaps I misunderstood what you are doing??

 

[John]

Hi Marshall - Thanks for your response.
OctAct (the item I want to allow to be null in future months) is the
numerator. A numerator should be able to be zero without error**.
Unfortunately, I am not able to get Access to destinquish between zero and
null (empty). It treats both cases as zero in the code as posted. But based
on the side note, they are different.

**side note re: errors - when I remove the "On Error Resume Next" line, it
errors out when I have zero or null in OctPlan (denominator) or null in
OctAct (numerator) but does not error out when there is zero in OctAct.

Thanks

 

[Marshall Barton]

Hi Marshall - Thanks for your response.
OctAct (the item I want to allow to be null in future months) is the
numerator. A numerator should be able to be zero without error**.
Unfortunately, I am not able to get Access to destinquish between zero and
null (empty). It treats both cases as zero in the code as posted. But based
on the side note, they are different.

**side note re: errors - when I remove the "On Error Resume Next" line, it
errors out when I have zero or null in OctPlan (denominator) or null in
OctAct (numerator) but does not error out when there is zero in OctAct.

You really should not use On Error Resume Next. You'll
won't know when your code falls on its face and starts
mangling data util it's too late to do anything about it.

I think(?) you can get the effect you want by using the Nz
function to convert a Null value to 0 or whatever is
appropriate to you calculation.

dblPercentage = (Nz(Me.PEPOctAct.Value,0) /
Me.PEPOctPlan.Value) * 100

 

[John]

Thanks Marshall. The Nz did not work out. Instead, I add a
Case 0 To 0.01
Me.PEPOctInd.Value = "N/A"
'Me.PEPOctInd.BackColor = vbTBD

This will cover the situation, although not perfect. Thanks for the time
and attention.