UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Max And Min Function, Access 2016    
 
   
haxan786
post May 9 2019, 06:42 PM
Post#1



Posts: 7
Joined: 8-May 19



Hello everyone

can anyone help me to convert excel expression to access. I wan to use it in calculated field.

=MAX(IF(OR(F1=0,F1<E1),
MIN(C1-F1,H1),
MAX(MIN(E1-F1,MAX(C1-F1,E1-F1),H1),0))
+IF(AND(F1>=E1,E1<C1,F1<=C1,F1>=D1,E1>=D1,D1>0),
IF(F1=E1,MIN(H1,C1-E1),
IF(F1>E1,
MIN(C1-E1,C1-E1*101.5%,H1),
0)),
0),0)

Thanks




Go to the top of the page
 
theDBguy
post May 9 2019, 07:39 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,425
Joined: 19-June 07
From: SunnySandyEggo


Hi. Since there are no equivalent functions in Access, you'll have to use custom ones. For example:

GetMinArrayValue() and GetMaxArrayValue()

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
gemmathehusky
post May 10 2019, 06:30 AM
Post#3


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


if it's a minimum (or maximum) value in a table, then

dmin("fieldname","tablename","optional filter")

the issue might be one of getting a properly normalised data structure, though, since dmin and dmax work on a VERTICAL data structure,
You shouldn't really need to compare numbers in the same row of a database table, but in a column.

ie you are comparing C1, D1, E1, F1 etc - it's hard to see how these can be relevant to a well structured database - can you clarify what these fields ARE.

in your example not if d1>e1, it might be that you need if D1>D2 - but getting from a horizontal spreadsheet to a vertical database table structure is not necessarily trivia.

(Although concepts like D1>D2 are not right in terms of a database either - hence TheDBGuy's maybe cryptic comment that there are no equivalent functions in access. There really aren't - databases really work on domain totals - ie extract a SET of 20 items, and then work out the total, the count, the average, the highest, the lowest, the earliest, etc of these 20 - or sort them and present them in some arbitrary order - you still have the same 20 items, but the order changes as required - which is why the concept of comparing item 1 with item 2 is not directly relevant or particularly easy to do - any of the 20 items can be regarded as item 1 and any item can be item 2)

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
kfield7
post May 10 2019, 08:10 AM
Post#4



Posts: 1,004
Joined: 12-November 03
From: Iowa Lot


It looks to me like everything is compared on the same row (1) in this Excel expression.
Therefore, converting this to query fields and aliases should be fairly straightforward.

first, deal with Min and Max. You can hard code these in the query for each instance, or you can use a VBA function.
This function will work with both text and numeric data:

function Min(a,b) as variant
if a<b then Min=a else Min=b
end function

Now converting your Excel columns to Access field names (substitute your actual field names):

=MAX(IIF(([FieldF] = 0) OR ([FieldF] < [FieldE]), MIN([FieldC] - [FieldF], [FieldH]), ...

you can continue with the rest.

Remember in Access queries and VBA, the Excel OR(a,b) equivalent is (a OR b), same with AND.

Also, note that sometimes it's more clear what's happening in a query (and easier to test) if you use aliases to break up the expression:

Alias1: MIN([FieldC]-[FieldE],[FieldC]-[FieldE]*1.015,[FieldH]),

Then use that Alias in a final Alias expression.

Go to the top of the page
 
haxan786
post May 10 2019, 10:38 AM
Post#5



Posts: 7
Joined: 8-May 19



Thanks everyone

I finally did it with custom function Maxoflist() and Minoflist().
Go to the top of the page
 
theDBguy
post May 10 2019, 10:55 AM
Post#6


Access Wiki and Forums Moderator
Posts: 76,425
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd October 2019 - 07:50 AM