Max of multiple columns

Status
Not open for further replies.
It would appear that you have not pasted the function into a module in your database.
 
It would appear that you have not pasted the function into a module in your database.

Please see attached screenshot. I wonder what is wrong...
 

Attachments

  • function.jpg
    function.jpg
    97.2 KB · Views: 332
I can't quite make out the code on your previous jpg.
Can you copy the sql of your query and paste it here?

You certainly did put the functions in a module.
 
Sure:

Code:
SELECT kdrivecrm701.kdriveno.F1, kdrivecrm701.CRMNumber, kdrivecrm701.OppName, kdrivecrm701.[Pricing Hours Est], IIf([kdrivecrm701]![LifecyclePhase]="6-Win",[kdrivecrm701]![Execution Date],[kdrivecrm701]![Date Closed]) AS [Close Date], [Deal Stage].F2 AS [Deal Stage], kdrivecrm701.[Contract Value(TCV+TBV)], pricinganalyst.F2 AS [Pricing Analyst], kdrivecrm701.[Scheduled RDG Date], kdrivecrm701.[Approved RDG Date], kdrivecrm701.[Pursuit Decision Scheduled], kdrivecrm701.[Bid Decision Scheduled], kdrivecrm701.[Validate Bid Scheduled], kdrivecrm701.[Submit Proposal Scheduled], kdrivecrm701.LevelIIScheduledDate
FROM [Deal Stage] RIGHT JOIN (pricinganalyst RIGHT JOIN kdrivecrm701 ON pricinganalyst.F1 = kdrivecrm701.PricingAnalyst) ON [Deal Stage].F1 = kdrivecrm701.LifecyclePhase
WHERE (((IIf([kdrivecrm701]![LifecyclePhase]="6-Win",[kdrivecrm701]![Execution Date],[kdrivecrm701]![Date Closed]))>#1/1/2012# Or (IIf([kdrivecrm701]![LifecyclePhase]="6-Win",[kdrivecrm701]![Execution Date],[kdrivecrm701]![Date Closed])) Is Null));
My goal is for it to take the maximum dates from the columns:

Scheduled RDG Date, Approved RDG Date, Pursuit Decision Scheduled, Bid Decision Scheduled, Validate Bid Scheduled, Submit Proposal Scheduled, and LevelIIScheduled Date...

Here is the code that I pasted in the module:

Code:
Option Compare Database

Public Function Minimum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
 
' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
 
' Cycle through each value from the row to find the smallest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I
 
' Return the minimum value found.
Minimum = currentVal
 
End Function
 
Public Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
 
' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
 
' Cycle through each value from the row to find the largest.
 
For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
Next I
 
' Return the maximum value found.
Maximum = currentVal
 
End Function

Is there something extra I need to do to get it to recognize the maximum function?
 
Last edited:
I don't see any Maximum(....) in your SQL. Also, the functions were written, as I understand things, to find maximum of a list of Numbers, and there was a followup to show how to adjust for text values.
 
I don't see any Maximum(....) in your SQL. Also, the functions were written, as I understand things, to find maximum of a list of Numbers, and there was a followup to show how to adjust for text values.

Hey JDraw, there wouldn't be a Maximum function in my SQL because it will not recognize the function altogether.

The dates are Numbers, not text values so I will not need to change the original code..

Do I need to do something extra in the latest version of Access to get the query to recognize the new "Maximum" function created in the module?
 
Not that I'm aware of. Once you paste the function into a module, you should be able to use the function.

I just pasted them in my database and tried it with dates

?Maximum(#3/21/2012#,#7/10/2013#,#3/23/2011#)
7/10/2013

Works perfect!!!
 
Last edited:
Not that I'm aware of. Once you paste the function into a module, you should be able to use the function.

I just pasted them in my database and tried it with dates

?Maximum(#3/21/2012#,#7/10/2013#,#3/23/2011#)
7/10/2013

Works perfect!!!

In the Design View, did you insert that formula in the Field or Criteria Section?
 
No I used the immediate window.
Control G to open the immediate window then

? to signify I'm looking for a response and then
Maximum(#3/21/2012#,#7/10/2013#,#3/23/2011#)

all on the same line, then hit return, and the response should appear
 
No I used the immediate window.
Control G to open the immediate window then

? to signify I'm looking for a response and then
Maximum(#3/21/2012#,#7/10/2013#,#3/23/2011#)

all on the same line, then hit return, and the response should appear

got it...it worked...it said my macros were disabled. LOL

Thanks!!! ALOT!
 
Glad you have it working. You didn't mention you were using macros, but I don't use them anyway.
 
i have to have 10 posts to post an image for my next question...so i need to post 5 more times
 
i have to have 10 posts to post an image for my next question...so i need to post 4 more times
 
i have to have 10 posts to post an image for my next question...so i need to post 3 more times
 
i have to have 10 posts to post an image for my next question...so i need to post 2 more times
 
i have to have 10 posts to post an image for my next question...so i need to post 1 more time
 
Dear dbDamo: thanks for the code you provided, but what I want to do is different,
I have a table like below:

F1 F2 F3 F4
20 0 90 100

I want to find the minimum number except zero and maximum number as follow;

Min Max Balance
20 100 80

By above min I mean 20 not zero,


Please help me.

Thanks in advance.
Khalil - Afghanistan








Ok, now that you know what you are doing and hopefully why you are doing it, I will tell you how -

Firstly, paste this code into a module -

Code:
Function Minimum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
 
' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
 
' Cycle through each value from the row to find the smallest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I
 
' Return the minimum value found.
Minimum = currentVal
 
End Function
 
Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
 
' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
 
' Cycle through each value from the row to find the largest.
 
For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
Next I
 
' Return the maximum value found.
Maximum = currentVal
 
End Function
Then create a query based on your table in design view. Drag your 4 fields into the query grid. In the fifth column of the query grid, type

Maximum value: Maximum([field1],[field2],[field3],[field4])

Now save and run your query!
 
Place an IF condition after the For I = 0 To UBound(FieldArray)
e.g.
For I = 0 To UBound(FieldArray)
IF FieldArray(I) > 0 then
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
END IF
Next I

You will need to do the same for the Maximum value
 
Dear Poppa Smurf:

Thanks for the post, I copy and paste the if function, but unfortunately it didn't work.

Please if possible make it more clear.


Khalil




Place an IF condition after the For I = 0 To UBound(FieldArray)
e.g.
For I = 0 To UBound(FieldArray)
IF FieldArray(I) > 0 then
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
END IF
Next I

You will need to do the same for the Maximum value
 
Status
Not open for further replies.

Users who are viewing this thread

Back
Top Bottom