how to change first to last in total query by a value of unbound field in a form (1 Viewer)

alaaaboulela

Registered User.
Local time
Yesterday, 22:02
Joined
Nov 22, 2019
Messages
11
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
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:02
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! I'm not sure I understand. Can you tell us more, please?
 

alaaaboulela

Registered User.
Local time
Yesterday, 22:02
Joined
Nov 22, 2019
Messages
11
thank you
please look at the picture
 

Attachments

  • qqq.jpg
    qqq.jpg
    91 KB · Views: 110

alaaaboulela

Registered User.
Local time
Yesterday, 22:02
Joined
Nov 22, 2019
Messages
11
sorry
it was too large
i uploaded it again please chech it
 

June7

AWF VIP
Local time
Yesterday, 21:02
Joined
Mar 9, 2014
Messages
5,424
I see an image and request still doesn't make sense to me.

What you show is modifying a query object.

Could possibly have IIf() expression in textbox on form or report that would return Sum or Max depending on some criteria.
 

alaaaboulela

Registered User.
Local time
Yesterday, 22:02
Joined
Nov 22, 2019
Messages
11
i want to change statue of the total in field of cost of goods
if combo box = max total change to max
if it = last total change to last , etc .
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:02
Joined
Oct 29, 2018
Messages
21,358
i want to change statue of the total in field of cost of goods
if combo box = max total change to max
if it = last total change to last , etc .

Hi. Check out the QueryDef object.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:02
Joined
Feb 28, 2001
Messages
27,001
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:

https://docs.microsoft.com/en-us/of...esktop-database-reference/querydef-object-dao

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.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:02
Joined
Oct 29, 2018
Messages
21,358
Hi. Good luck with your project.
 

Users who are viewing this thread

Top Bottom