Crosstab Value field formula too long, min() and max() function help sought

[nclsgna via AccessMonster.com]

If i put my fomula in the "Value" field, Access is truncating my fomula
saying its too long.

If i split the fomula into different calculated fields (that do not display
when you view in datasheet view) which i then add in the calculated "Value"
field, Access does not recognise the calculated fields feeding into the
fomula. I have tried declaring explicitly the fields but that creates null
values in the fomula.

As a way of getting round the lengthy code i though i could just do a fomula
that compares two values as follows:

Minimum of ([Transaction Amount] and <<Maximum of ([Differential] and Zero)>>)


You will see that the Max() function is nested in the fomula, its result
being the second variable in the Min fomula.

Is there a way of doing this?

Also, is it possible to use the Min and Max functions in a crosstab query,
where it is comparing two values, not to a single field in an external table?
if so whats the possible sample code?

 

[Duane Hookom]

I hate to ask, but can you tell us what your expression is that is too long?
I (and possibly others) don't really have a clue what you are attempting to
calculate since your "Minimum of(....)" expression doesn't make sense.

 

[nclsgna via AccessMonster.com]

Initially I had an IIf formula where it compares [Transaction Amount] and
[Above Limit]

1. If [Above Limit] is negative [Limit Available] =0,
2. If [Above Limit]>[Transaction Amount]; [Limit Available]=0
3. If [Above Limit]<[Transaction Amount]; [Limit Available]=[Transaction
Amount]-[Above Limit] (differential)
4. [Transaction Amount]

The result of the formula is either a zero value, [transaction amount],or
[Differential]

[Limit Available] would be a query, same as [Above Limit], which resulted in
a cumbersome formula. When I tried to have the sub-queries as calculated
fields in the Crosstab query I was getting prompts for the query expressions.

I thought of using the Min() and Max() functions as a way round the lengthy
IIf function.

Is it possible to have Max() and Min() functions nested within each other,
comparing two values each in a crosstab query?

I hate to ask, but can you tell us what your expression is that is too long?
I (and possibly others) don't really have a clue what you are attempting to
calculate since your "Minimum of(....)" expression doesn't make sense.

If i put my fomula in the "Value" field, Access is truncating my fomula
saying its too long.

[quoted text clipped - 23 lines]

table?
if so whats the possible sample code?

 

[Duane Hookom]

Have you tried writing a small function that accepts the Above Limit and
Transaction Amount and returns the proper Limit Available?

--
Duane Hookom
MS Access MVP
--

Initially I had an IIf formula where it compares [Transaction Amount] and
[Above Limit]

1. If [Above Limit] is negative [Limit Available] =0,
2. If [Above Limit]>[Transaction Amount]; [Limit Available]=0
3. If [Above Limit]<[Transaction Amount]; [Limit Available]=[Transaction
Amount]-[Above Limit] (differential)
4. [Transaction Amount]

The result of the formula is either a zero value, [transaction amount],or
[Differential]

[Limit Available] would be a query, same as [Above Limit], which resulted
in
a cumbersome formula. When I tried to have the sub-queries as calculated
fields in the Crosstab query I was getting prompts for the query
expressions.

I thought of using the Min() and Max() functions as a way round the
lengthy
IIf function.

Is it possible to have Max() and Min() functions nested within each other,
comparing two values each in a crosstab query?

I hate to ask, but can you tell us what your expression is that is too
long?
I (and possibly others) don't really have a clue what you are attempting
to
calculate since your "Minimum of(....)" expression doesn't make sense.

If i put my fomula in the "Value" field, Access is truncating my fomula
saying its too long.

[quoted text clipped - 23 lines]

table?
if so whats the possible sample code?

 

[nclsgna via AccessMonster.com]

I do not know any VBA so am stuck.

Have you tried writing a small function that accepts the Above Limit and
Transaction Amount and returns the proper Limit Available?

Initially I had an IIf formula where it compares [Transaction Amount] and
[Above Limit]

[quoted text clipped - 32 lines]

 

[Duane Hookom]

Time to learn a little VBA...
Open a new, blank module and paste the below code from the "Function..." to
the "End Function" lines. Save the module as "basCalcs". You can test this
function by opening the debug window (press Ctrl+G) and enter:
+--------------------
| ?GetLimitAvail(100, 200)
|
Enter different values for the 100 and 200 to test the return values. If the
returned value doesn't meet your business rules, you should be able to make
some modifications. The Select Case sets up a series of conditions looking
for a true expression. When a true expression is found, the function returns
a value.

When your function works, you can add it into your query replacing the two
arguments with field names.
TheValue: GetLimitAvail([Above Limit], [Transaction Amount])

This function assumes the fields and values are double precision numbers.

Function GetLimitAvail(pdblAboveLimit As Double, _
pdblTransAmt As Double) As Double
'1. If [Above Limit] is negative [Limit Available] =0,
'2. If [Above Limit]>[Transaction Amount]; [Limit Available]=0
'3. If [Above Limit]<[Transaction Amount];
' [Limit Available]=[TransactionAmount]-[Above Limit]
' (differential)
'4. [Transaction Amount]

'The result of the formula is either a zero value,
' [transaction amount],or [Differential]

Select Case True
Case pdblAboveLimit < 0 Or _
pdblAboveLimit > pdblTransAmt
GetLimitAvail = 0
Case pdblAboveLimit < pdblTransAmt
GetLimitAvail = pdblTransAmt - pdblAboveLimit
Case Else
GetLimitAvail = pdblTransAmt
End Select
End Function


--
Duane Hookom
MS Access MVP
--

I do not know any VBA so am stuck.

Have you tried writing a small function that accepts the Above Limit and
Transaction Amount and returns the proper Limit Available?

Initially I had an IIf formula where it compares [Transaction Amount]
and
[Above Limit]

[quoted text clipped - 32 lines]

table?
if so whats the possible sample code?