It would appear that you have not pasted the function into a module in your database.
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));
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
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.
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!!!
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
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 -
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, typeCode: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
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