Maximum value from a recordset

[lynns]

I am using the following code to read a sub set of records from the data table. I need to find the minimun and maximum values of Xdim and Ydim. How can I use the Dmin and Dmax functions to find the Minimum and Maximum value for the values for variable rs(2) and variable rs(3)?

SQL = "SELECT ID, GroupID, XDim, YDim FROM DimensionsTable WHERE (((GroupID)=1)); "

Set rs = db.OpenRecordset(SQL)

X1 = rs(2)
Y1 = rs(3)

Thanks

Lynn Sipes

 

[rac2]

Maybe -

SQL = "SELECT ID, GroupID,
DMax("[XDim]","[DimensionsTable]","[GroupID]=1") AS HighX,
DMin("[XDim]","[DimensionsTable]","[GroupID]=1") AS LowX,
DMax("[YDim]","[DimensionsTable]","[GroupID]=1") AS HighY,
DMin("[YDim]","[DimensionsTable]","[GroupID]=1") AS LowY

FROM DimensionsTable; "

Set rs = db.OpenRecordset(SQL)

MaximumX = rs("HIghX")
MaximumY = rs("HighY")
MinimumX = rs("LowX")
MinimumY = rs("LowY")

Please let me know whether this works.

 

[lynns]

rac2

Your post was helpful. It helped me arrive at the solution that I finally used. As you can see from the code below I wanted to use a variable dependant upon the current record being viewed, [Forms]![Header]![GroupId]. When I put my variable name in place of the GroupID variable in your code I had a terrible time resolving all those single-double quote issues and found that this works as well without all those quotes to deal with. Thanks for the leg up.
Lynn S

SqlM = "SELECT Max(XDim) AS MaxOfXDim, Min(XDim) AS MinOfXDim, Max(YDim) AS MaxOfYDim, Min(YDim) AS MinOfYDim FROM DataTable WHERE (((GroupID)= " & [Forms]![GroupID]![GroupID] & &quot AND ((Exclude)=0));"
Dim rsm As Recordset
Set rsm = db.OpenRecordset(SqlM)
MaxX = rsm(0)
MinX = rsm(1)
MaxY = rsm(2)
MinY = rsm(3)