hi
how to change first to last in total (options) query by a value of unbound field in a form ( last / first )
or make it depends on combo box contains values like max , first and last
thank you
It seems to me that you are asking how to change the function being used as an SQL aggregate in a query. You named First, Last, Max, and (implied) Sum.
Normally, you do this by modifying the query. If you have a named query defined, you have to modify the SQL statement. For an example of how to do that, see this link:
By the way, while you certainly CAN select First or Last as query keywords, beware that they are insidiously misleading. If the query doesn't include an ORDER BY then the First and Last SQL aggregate functions have no practical meaning.
If this were NOT a querydef object but instead was a simple SQL string, you would break the string into two parts, then do something similar to
Code:
SQLPart1 = "SELECT "
SQLPart2 = "(Cost) FROM MyTable ORDER BY RecDate ;"
...
<presumes a combo box named SQLKwd has been used for selecting the function>
SQLTotal = SQLPart1 & Trim$(Me.SQLKwd) & SQLPart2
Note carefully that there is no leading space in the second part of the string because you want to concatenate the selected keyword to a parenthetical argument. Your combo would have to return a valid SQL aggregate. What you do with the resultant string would relate back to the article I posted for you.
Now, returning to my warning: If you take data directly from a table, be aware that due to record "churning" caused by updates, the records will not be in any recognizable order. If you use a query but the query doesn't have an ORDER BY clause, you STILL will have no particular order. In such a context, First and Last are almost random. They CERTAINLY are unpredictable. Max, Sum, Avg, Min, and a couple of other aggregates still make sense, but not First or Last.