Max of multiple columns (3 Viewers)

[jdraw]

It would appear that you have not pasted the function into a module in your database.

 

[copleyr]

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...

 

[jdraw]

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.

 

[copleyr]

Sure:

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:

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?

 

[jdraw]

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.

 

[copleyr]

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?

 

[jdraw]

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!!!

 

[copleyr]

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?

 

[jdraw]

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

 

[copleyr]

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!

 

[jdraw]

Glad you have it working. You didn't mention you were using macros, but I don't use them anyway.

 

[copleyr]

btw, i now know what control G means lol.

 

[copleyr]

i have to have 10 posts to post an image for my next question...so i need to post 5 more times

 

[copleyr]

i have to have 10 posts to post an image for my next question...so i need to post 4 more times

 

[copleyr]

i have to have 10 posts to post an image for my next question...so i need to post 3 more times

 

[copleyr]

i have to have 10 posts to post an image for my next question...so i need to post 2 more times

 

[copleyr]

i have to have 10 posts to post an image for my next question...so i need to post 1 more time

 

[Khalil Islamzada]

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 -

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!

 

[Poppa Smurf]

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

 

[Khalil Islamzada]

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