Max function

  • Thread starter Thread starter Derek Wittman
  • Start date Start date
D

Derek Wittman

Background: I would like to compare:
[MCS_mdm_oper].{manl_allow_pct] and
[MCS_mdm_oper].[prcs_allow_pct] displaying whichever is
greater in the query field in datasheet view. Can someone
please help me out?

I have little SQL programming and no VBA experience.

Thank you!
Derek
 
Put the following function in a regular module:

Public Function MaximumValue(varValue1 As Variant, varValue2 As Variant) As
Variant
If varValue1 > varValue2 Then
MaximumValue = varValue1
Else
MaximumValue = varValue2
End If
End Function

Then use this in your query:
DisplayField: MaximumValue([MCS_mdm_oper].{manl_allow_pct],
[MCS_mdm_oper].[prcs_allow_pct])
 
Add a cell to your query

Field: Greatest: IIF(NZ([MCS_mdm_oper].[manl_allow_pct]) >
NZ([MCS_mdm_oper].[prcs_allow_pct]),
[MCS_mdm_oper].{manl_allow_pct],
[MCS_mdm_oper].[prcs_allow_pct])

All on one line.
 
It is clear that you are comparing two fields from the
same table [MCS_mdm_oper]. To show only the one which is
greater, place this formula in your query:

GreaterOne: iif ([MCS_mdm_oper].[manl_allow_pct]>
[MCS_mdm_oper].[prcs_allow_pct], [MCS_mdm_oper].
[manl_allow_pct],]>[MCS_mdm_oper].[prcs_allow_pct])

This is way faster than writing VB function and calling
it fron the query.

:-)
 
First I want to thank those who responded so quickly. I
think this is the first time I've posted to this ng, and
the best turnaround time I've experienced.

From the formatting, it looks extremely similar to an if
function in Excel, save the NZ functions. This looks to
be exactly what I need. Thank you again!
-----Original Message-----
Add a cell to your query

Field: Greatest: IIF(NZ([MCS_mdm_oper].[manl_allow_pct])

NZ([MCS_mdm_oper].[prcs_allow_pct]),
[MCS_mdm_oper].{manl_allow_pct],
[MCS_mdm_oper].[prcs_allow_pct])

All on one line.

Derek said:
Background: I would like to compare:
[MCS_mdm_oper].{manl_allow_pct] and
[MCS_mdm_oper].[prcs_allow_pct] displaying whichever is
greater in the query field in datasheet view. Can someone
please help me out?

I have little SQL programming and no VBA experience.

Thank you!
Derek
Click to expand...
.
Click to expand...
 
Back
Top