Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Limit Digits on Text Box in Output 1

Status
Not open for further replies.

JStrand

Technical User
Nov 14, 2011
44
US
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.
 
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
 
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.

 
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?
Code:
... , "Budget" AS ActBud, ...

Duane
Hook'D on Access
MS Access MVP
 
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.
 
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.
 
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
 
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.
 
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:
Code:
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
 
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
 
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
 
It's all in the format property. This should work.
Code:
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
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top