Limit Digits on Text Box in Output 1

[JStrand]

I have many text boxes on a crosstab report which have percent calculations. In some cases the result of a calculation will exceed 6 digits, such as 647925%. I want to be able to control the outcome to lets say 6 digits, and a question mark if that size is exceeded. Right now if the width of the text box is exceeded the result is #########. Where would I place such a control and what would it look like? Any help would be appreciated.

 

[dhookom]

I would probably place this in the crosstab as the value. If the value was greater than 6 digits, you could return null or a negative number which could be displayed as a ? in the report.

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Thanks Duane. Currently I'm not summarizing the data on the Cross tab enough to perform this test at that level.

My Cross tab groups and categorizes the specific branch data by period 1-12 with SumOfCurrentPeriod as the value.

TRANSFORM Sum(BranchMasterTbl.[SumOfCurrent Period Budget $]) AS Amount
SELECT BranchMasterTbl.Division, BranchMasterTbl.DivisionName, BranchMasterTbl.Region, BranchMasterTbl.RegionName, BranchMasterTbl.Branch, BranchMasterTbl.BranchName, BranchMasterTbl.Category, BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, "Budget" AS ActBud, [ReportGroup3] & "Budget" AS ReportGroupBook, Sum(BranchMasterTbl.[SumOfCurrent Period Budget $]) AS YTDAmount
FROM BranchMasterTbl
GROUP BY BranchMasterTbl.Division, BranchMasterTbl.DivisionName, BranchMasterTbl.Region, BranchMasterTbl.RegionName, BranchMasterTbl.Branch, BranchMasterTbl.BranchName, BranchMasterTbl.Category, BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, "Budget", [ReportGroup3] & "Budget"
ORDER BY BranchMasterTbl.Division, BranchMasterTbl.DivisionName, BranchMasterTbl.Region, BranchMasterTbl.RegionName, BranchMasterTbl.Branch, BranchMasterTbl.BranchName, BranchMasterTbl.Category, BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr
PIVOT BranchMasterTbl.Period In (1,2,3,4,5,6,7,8,9,10,11,12);


Then within a specific line on the Report (example Budgeted Operating Profit Percent) for January I have the calculation which generates the value which could produce a result with more than 6 digits if the denominator happens to be very small for that branch and period.

=IIf(Sum(IIf([Ctr]<9,IIf([ScorecardLine]<23,IIf([ActBud]="Budget",([1]/1000*-1),0),0)))=0,0,IIf(Sum(IIf([Ctr]<9,IIf([ScorecardLine]<6,IIf([ActBud]="Budget",([1]/1000*-1),0),0)))=0,0,(Sum(IIf([Ctr]<9,IIf([ScorecardLine]<23,IIf([ActBud]="Budget",([1]/1000*-1),0),0))))/(Abs(Sum(IIf([Ctr]<9,IIf([ScorecardLine]<6,IIf([ActBud]="Budget",([1]/1000*-1),0),0)))))))

My guess is that I need to create the format within this last chunk of code which has several nested IIf statements which complicates the matter.

 

[dhookom]

I prefer to not attempt to unravel that expression. However, why have all of the IIf() that check ActBud="Budget" when this seems to be a hard-coded value in your crosstab?

... , "Budget" AS ActBud, ...

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

I have 5 Crosstab's unioned. The union Qry is the datasource for the report. ActBud can equal Actual, Budget, PriorYear, LYFull Year, TY FY Budget. I need to distinguish which ActBud is to be used in the appropriate text box calcs.

 

[dhookom]

Based on the complexity of the expressions, I would probably loop through the controls in the on format event and set the format property based on the values.

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Thanks Duane. I'll try to test this on one control in the on format event. I'll do some searching on syntax for setting the format property based on the values exceeding a certain number of characters.

 

[dhookom]

I would set the tag of every control that needs this modification. Then run some code in the on Format event of the section that loops through all controls looking for the specific value in the tag. Set the control's format based on the value.

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

I have all my controls tagged. I'm having difficulty finding the SQL to test the value. One such tag is [ScheduleADBudget1]. Do you know of any references for SQL on testing where the value exceeds a certain number of digit? This Control is formatted as a percentage with one decimal.

 

[dhookom]

There would be no changes to any SQL. Assuming you entered "Format" into the tag of every control that you want to vary, try code like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim ctl As Control
    For Each ctl In Me.Controls
        If InStr(1, ctl.Tag, "Format") > 0 Then[green]
            'change the 10 below to your value[/green]
            If ctl.Value > 10 Then
                ctl.Format = """?"";(0.00);;"
                ctl.DecimalPlaces = 1
             Else
                ctl.Format = "standard"
                ctl.DecimalPlaces = 2
                
            End If
        End If
    Next
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Thanks Duane. I've altered the names as to include "format" such as [ScheduleADBudget1Format] which is used in my January column.
Does modifying the tag achieve the convention you mentioned?

I've added the code on the format event of the Report Footer where my text boxes are but getting the same result which exceeds the size of my test box, therefore ######## appears. The result when I increase the width of the test box is 221120.4% which I want to be represented as a "?" or "*" instead of the # signs.

I must be missing something.

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If InStr(1, ctl.Tag, "Format") > 0 Then
'change the 10 below to your value
If ctl.Value > 10 Then
ctl.Format = """?"";(0.00);;"
ctl.DecimalPlaces = 1
Else
ctl.Format = "standard"
ctl.DecimalPlaces = 2

End If
End If
Next
End Sub

 

[dhookom]

altered the names

I only expected you to update the Tag property of the text boxes on the report. You should not have changed anything else.

You also need to change the code since 10 is 1000%.

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Thanks Duane. I hadn't used the tag property before. This works perfect for converting the values above 1000% to "?". I'm having difficulty coming up with a test for +/-1000%. I attempted using Abs on the ctl.value statement but it is not picking up the negative as being >10.


Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If InStr(1, ctl.Tag, "Format") > 0 Then
'change the 10 below to your value
If Abs(ctl.Value) > 10 Then
ctl.Format = """?"";0.00%;;"
ctl.DecimalPlaces = 0
Else
ctl.Format = "percent"
ctl.DecimalPlaces = 0

End If
End If
Next

 

[dhookom]

It's all in the format property. This should work.

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
    For Each ctl In Me.Controls
        If InStr(1, ctl.Tag, "Format") > 0 Then
            'change the 10 below to your value
                If Abs(ctl.Value) > 10 Then
                ctl.Format = """?"";""?"";;"
                ctl.DecimalPlaces = 0
             Else
                ctl.Format = "percent"
                ctl.DecimalPlaces = 0
                
            End If
        End If
    Next

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Thanks Duane. This works perfectly. I'm not quite sure why the format didn't work based on the previous code. It seems like since the abs value was >10 the first format should have applied and yielded a ?. Why did the line require an additional ""?"" .

Thanks again.

 

[dhookom]

If you check Help on the format property of a numeric control, it describes there are 4 possible sections of the format string. They are for Positive, Negative, Zero, and Null values. You needed to set the Negative section to "?".

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Very helpful. Thanks Again Duane.