need help with access query

[karoaustin]

I have set up a query to get the oldest date from a set of dates across 7
columns for each row in a table. But I keep getting an error that there is a
syntax error (comma) when I try to save or run the query. Here is the query.
Any help will be greatly appreciated.

=Max(([Backup4/5/02]![Date Due],[Backup4/5/02]![1st
Extension],[Backup4/5/02]![2nd Extension],[Backup4/5/02]![3rd
Extension],[Backup4/5/02]![4th Extension],[Backup4/5/02]![5th
Extension],[Backup4/5/02]![6th Extension]))

Thanks!

 

[Ken Snell [MVP]]

What you've posted is not a query. It's an expression that would be used to
get the Maximum value of a single field from a recordset.

You're trying to use the Max aggregate function to get the maximum of a
number of fields; it will not do that.

ACCESS has no built-in Max-like function. You'lll need to use your own
function and then call it from wherever you're using this expression.

Put this function in a regular module (name the module basFunctions):

Public Function MaxValueVariantArray(ParamArray ValuesArray() As Variant) As
Variant
' ** THIS FUNCTION RETURNS THE MAXIMUM VALUE FROM AN ARRAY OF VALUES.
' K.D.Snell 11/29/2004
Dim xlngLB As Long, xlngUB As Long, xlngCnt As Long
Dim xvarTp As Variant
xlngLB = LBound(ValuesArray)
xlngUB = UBound(ValuesArray)
If xlngUB >= 0 And xlngLB >= 0 Then
xvarTp = ValuesArray(xlngLB)
For xlngCnt = xlngLB + 1 To xlngUB
If ValuesArray(xlngCnt) > xvarTp Then xvarTp = ValuesArray(xlngCnt)
Next xlngCnt
End If
MaxValueVariantArray = xvarTp
End Function


Then use this expression wherever you have the current one:

=MaxValueVariantArray(([Backup4/5/02]![Date Due],[Backup4/5/02]![1st
Extension],[Backup4/5/02]![2nd Extension], Backup4/5/02]![3rd
Extension],[Backup4/5/02]![4th Extension],[Backup4/5/02]![5th
Extension],[Backup4/5/02]![6th Extension]))

 

[karoaustin]

Thank you! Worked like a charm and now I have a template for creating more
user functions.. You truly are a MVP.

What you've posted is not a query. It's an expression that would be used to
get the Maximum value of a single field from a recordset.

You're trying to use the Max aggregate function to get the maximum of a
number of fields; it will not do that.

ACCESS has no built-in Max-like function. You'lll need to use your own
function and then call it from wherever you're using this expression.

Put this function in a regular module (name the module basFunctions):

Public Function MaxValueVariantArray(ParamArray ValuesArray() As Variant) As
Variant
' ** THIS FUNCTION RETURNS THE MAXIMUM VALUE FROM AN ARRAY OF VALUES.
' K.D.Snell 11/29/2004
Dim xlngLB As Long, xlngUB As Long, xlngCnt As Long
Dim xvarTp As Variant
xlngLB = LBound(ValuesArray)
xlngUB = UBound(ValuesArray)
If xlngUB >= 0 And xlngLB >= 0 Then
xvarTp = ValuesArray(xlngLB)
For xlngCnt = xlngLB + 1 To xlngUB
If ValuesArray(xlngCnt) > xvarTp Then xvarTp = ValuesArray(xlngCnt)
Next xlngCnt
End If
MaxValueVariantArray = xvarTp
End Function


Then use this expression wherever you have the current one:

=MaxValueVariantArray(([Backup4/5/02]![Date Due],[Backup4/5/02]![1st
Extension],[Backup4/5/02]![2nd Extension], Backup4/5/02]![3rd
Extension],[Backup4/5/02]![4th Extension],[Backup4/5/02]![5th
Extension],[Backup4/5/02]![6th Extension]))


--

Ken Snell
<MS ACCESS MVP>

I have set up a query to get the oldest date from a set of dates across 7
columns for each row in a table. But I keep getting an error that there is a
syntax error (comma) when I try to save or run the query. Here is the query.
Any help will be greatly appreciated.

=Max(([Backup4/5/02]![Date Due],[Backup4/5/02]![1st
Extension],[Backup4/5/02]![2nd Extension],[Backup4/5/02]![3rd
Extension],[Backup4/5/02]![4th Extension],[Backup4/5/02]![5th
Extension],[Backup4/5/02]![6th Extension]))

Thanks!

 

[Ken Snell [MVP]]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Thank you! Worked like a charm and now I have a template for creating more
user functions.. You truly are a MVP.

What you've posted is not a query. It's an expression that would be used to
get the Maximum value of a single field from a recordset.

You're trying to use the Max aggregate function to get the maximum of a
number of fields; it will not do that.

ACCESS has no built-in Max-like function. You'lll need to use your own
function and then call it from wherever you're using this expression.

Put this function in a regular module (name the module basFunctions):

Public Function MaxValueVariantArray(ParamArray ValuesArray() As Variant) As
Variant
' ** THIS FUNCTION RETURNS THE MAXIMUM VALUE FROM AN ARRAY OF VALUES.
' K.D.Snell 11/29/2004
Dim xlngLB As Long, xlngUB As Long, xlngCnt As Long
Dim xvarTp As Variant
xlngLB = LBound(ValuesArray)
xlngUB = UBound(ValuesArray)
If xlngUB >= 0 And xlngLB >= 0 Then
xvarTp = ValuesArray(xlngLB)
For xlngCnt = xlngLB + 1 To xlngUB
If ValuesArray(xlngCnt) > xvarTp Then xvarTp = ValuesArray(xlngCnt)
Next xlngCnt
End If
MaxValueVariantArray = xvarTp
End Function


Then use this expression wherever you have the current one:

=MaxValueVariantArray(([Backup4/5/02]![Date Due],[Backup4/5/02]![1st
Extension],[Backup4/5/02]![2nd Extension], Backup4/5/02]![3rd
Extension],[Backup4/5/02]![4th Extension],[Backup4/5/02]![5th
Extension],[Backup4/5/02]![6th Extension]))


--

Ken Snell
<MS ACCESS MVP>

I have set up a query to get the oldest date from a set of dates across 7
columns for each row in a table. But I keep getting an error that

there
is a

syntax error (comma) when I try to save or run the query. Here is the query.
Any help will be greatly appreciated.

=Max(([Backup4/5/02]![Date Due],[Backup4/5/02]![1st
Extension],[Backup4/5/02]![2nd Extension],[Backup4/5/02]![3rd
Extension],[Backup4/5/02]![4th Extension],[Backup4/5/02]![5th
Extension],[Backup4/5/02]![6th Extension]))

Thanks!