How do I get Access to display N/A

[UongSaki]

Hi all,

How do I get Access to display N/A on a report when a calculated value as a percentage is greater than, let’s say, 1000% using code? I cannot add another if statement in my formula as access refuses to accept it. My formula is too long.

Thank you,

UongSaki

 

[PaulF]

why not create a function and pass the field value(s) to function.

this example uses the Orders Details table from the Northwind database and sends the Unit Price and Quantity values to the function (in the code window behind the form / report)

ControlSource for the Textbox is
=fValue([UnitPrice],[Quantity])

the function is

Function fValue(dblUP As Double, intQty As Integer)
If Val(Nz(dblUP, 0)) * Val(Nz(intQty, 0)) > 100 Then
fValue = "N/A"
Else
fValue = Val(Nz(dblUP, 0)) * Val(Nz(intQty, 0))
End If
End Function


This way you don't have to worry about how long the formula is.
PaulF

 

[UongSaki]

Hi PaulF,

Thanks for the tip. It makes sense when I look at your code but I'm at a lost when trying to write a function for my report. Here's my ControlSource for the textbox:

=IIf([YtdBudgetCEType]=0,"N/A",IIf([CETypeSort]="Reimbursements" And Abs(Sum(Abs([BudgetActualCommitments]="Actual" And [FY]="FY05" And [CETypeSort]="Reimbursements" And [Period]<=[CurrentPeriod])*[Amount])/1000)<Abs(Sum(Abs([BudgetActualCommitments]="Budget" And [FY]="FY05" And [CETypeSort]="Reimbursements" And [Period]<=[CurrentPeriod])*[Amount])/1000),-1*[text63]/[YtdBudgetCEType],[text63]/[YtdBudgetCEType]))

Any tip on how a function can be contructed?

Thank you,

UongSaki

 

[PaulF]

these don't make sense to me

Abs(Sum(Abs([BudgetActualCommitments]="Actual"


[Period]<=[CurrentPeriod])*[Amount])/1000)
<Abs(Sum(Abs([BudgetActualCommitments]="Budget"


you're comparing numeric values to Strings, this could be why you're getting the too complex error statement

PaulF

 

[UongSaki]

May be you know the better way to this but I'm comparing the sum of Amount devided by 1,000 when all these conditions are true:

BudgetActualCommitments = Actual,
FY = FY05,
CETypeSort = Reimbursements, and
Period <=CurrentPeriod

Abs(Sum(Abs([BudgetActualCommitments]="Actual" And [FY]="FY05" And [CETypeSort]="Reimbursements" And [Period]<=[CurrentPeriod])*[Amount])/1000)

to the sum of Amount if

BudgetActualCommitments = Budget,
FY = FY05,
CETypeSort = Reimbursements, and
Period <=CurrentPeriod

<Abs(Sum(Abs([BudgetActualCommitments]="Budget" And [FY]="FY05" And [CETypeSort]="Reimbursements" And [Period]<=[CurrentPeriod])*[Amount])/1000)

I hope it makes sense.

Thanks,

UongSaki

 

[PaulF]

I'm lost, but thats nothing new I'm not sure how this is being used in the report but.... maybe using DSum() might be the way to go if you're summing the value of the field Amount in a recordset when the Field BudgetActualCommitments = "Budget" and again when it = "Actual", then you can compare those results
dim dblActual as Double, dblBudget as Double
dblActual = DSum("[TableName]![Actual]/1000","[TableName]",
[TableName]![BudgetActualCommitments] = 'Actual' And [TableName]![FY] = 'FY05' And [TableName]![CETypeSort] = 'Reimbursements' And [TableName]![Period] <= [TableName]![CurrentPeriod])

dblBudget = DSum("[TableName]![Actual]","[TableName]",
[TableName]![BudgetActualCommitments] = 'Budget' And [TableName]![FY] = 'FY05' And [TableName]![CETypeSort] = 'Reimbursements' And [TableName]![Period] <= [TableName]![CurrentPeriod])


PaulF