DMax sorting incorrectly

  • Thread starter Thread starter nybaseball22
  • Start date Start date
N

nybaseball22

Hello. I have two unbound text boxes on a form that opens as a
subform on a main form. I have a DMax and DMin string pointing to a
query. I have the query with a descending sort on one of the fields.
When the boxes look to the query, they sometimes come up out of
order. I have tried sorting both ascending and descending and the
same problem is occuring.

The query always sorts correctly, so I don't know why the form is
pulling them out of order. Is there a wya I can code the first text
box to take the first record in the query, and the second text box to
take the second record in the query?

The form is unbound because it uses DMax, Dmin, and DLookup from
several different queries.

Thanks.

This is the code I am using for the text boxes.

=DMax("[Station]","AutosGasStats1Station")
=DMin("[Station]","AutosGasStats1Station")
 
Post the SQL statement of the query. I am guessing that the sort order is
caused by the query referencing the textboxes on the form as parameters, and
sometimes the query cannot identify what data type those textboxes' values
are.
 
Thanks Ken. Here is the code.

SELECT TOP 2 [Automobiles-GasolineMain].Car, [Automobiles-
GasolineMain].Station, Count([Automobiles-GasolineMain].Station) AS
CountOfStation, Sum([Automobiles-GasolineMain].[Gallons Pumped]) AS
[SumOfGallons Pumped], Avg([Automobiles-GasolineMain].PPG) AS
AvgOfPPG, Sum([Gallons Pumped]*[PPG]) AS [Total Spend]
FROM [Automobiles-GasolineMain]
GROUP BY [Automobiles-GasolineMain].Car, [Automobiles-
GasolineMain].Station
HAVING ((([Automobiles-GasolineMain].Car)="CRV"))
ORDER BY Count([Automobiles-GasolineMain].Station) DESC;
 
Hi -

Your DMAX and DMIN functions are getting the max and min of the "Station"
field in the query, but these may or may not be in the first and second
records.

Don't you want the max and min of "Countofstation", since that is what you
are sorting the query on?

=DMax("[CountofStation]","AutosGasStats1Station")
=DMin("[CountofStation]","AutosGasStats1Station")

However, if you want to have your text boxes contain the values of the
"Station" field corresponding to the first and second records in the query,
DMAX and DMIN won't work. You could use the dmax and dmin functions inside a
dlookup:

=dlookup("[Station]","[AutosGasStats1Station]","[countofstation] = " & DMax("
[CountofStation]","AutosGasStats1Station"))

and

=dlookup("[Station]","[AutosGasStats1Station]","[countofstation] = " & DMin("
[CountofStation]","AutosGasStats1Station"))

John



Thanks Ken. Here is the code.

SELECT TOP 2 [Automobiles-GasolineMain].Car, [Automobiles-
GasolineMain].Station, Count([Automobiles-GasolineMain].Station) AS
CountOfStation, Sum([Automobiles-GasolineMain].[Gallons Pumped]) AS
[SumOfGallons Pumped], Avg([Automobiles-GasolineMain].PPG) AS
AvgOfPPG, Sum([Gallons Pumped]*[PPG]) AS [Total Spend]
FROM [Automobiles-GasolineMain]
GROUP BY [Automobiles-GasolineMain].Car, [Automobiles-
GasolineMain].Station
HAVING ((([Automobiles-GasolineMain].Car)="CRV"))
ORDER BY Count([Automobiles-GasolineMain].Station) DESC;
Click to expand...
 
Back
Top