Access calculated field expression (need math function)

[ebolbol]

Hi folks, hope you're all well.

I have scoured the internet in search of what Access' equivalent is to Excel's =MIN function in a formula. NOTHING.

I'm trying to calculate work hours in an Access table with calculated fields to calculate Regular time, over time, and double time respectively. As you can see, the Excel formulas below make it easy to do this.

I am so close with Access, but I can't seem to find the =MIN function, which in Excel, returns the smallest number in a set of values or formula.


Time In Time Out Less Meal
6:00 AM 8:30 PM 0.50





EXCEL (correct)



Regular time results = 8
=MIN(((TimeOut-TimeIn)*24)-LessMeal,8)


Over time results = 4
=MIN((((TimeOut-TimeIn)*24)-LessMeal)-RegHrs,4)


Double time = 2
=(((TimeOut-TimeIn)*24)-LessMeal)-RegHrs-OTHrs




ACCESS (not correct)

Regular time results = 14
((([TimeOut]-[TimeIn])*24)-[LessMeal],8)


and since OT and DT are calculated based on RegHrs it affects those hours too. Any help would be appreciated.

Ed

[mike60smart]

Hi
The following example calculates the average discount amount of all orders in the Northwind.mdb database. It multiplies the values in the UnitPrice and Discount fields to determine the discount amount of each order and then calculates the average. You can use this expression in an SQL statement in Visual Basic code:
SELECT Avg(UnitPrice * Discount) AS [Average Discount] FROM [Order Details];

[Welshgasman]

I would probably use an IIF() function.

[June7]

Access Min() is an aggregate function. If you need to compare values in same record and take the lowest, you have to build your own. An IIf() expression might be adequate, otherwise write a VBA custom function that can be called from query or textbox.

[ebolbol]

Hi
The following example calculates the average discount amount of all orders in the Northwind.mdb database. It multiplies the values in the UnitPrice and Discount fields to determine the discount amount of each order and then calculates the average. You can use this expression in an SQL statement in Visual Basic code:
SELECT Avg(UnitPrice * Discount) AS [Average Discount] FROM [Order Details];

Thank you for your reply. I'm really confused what average discount amount has to do with time calculation in a calculated field in a table and why I have to use VB code to accomplish this.

There has to be a simple way to do this.

[mike60smart]

The example is for calculating an Avg
Replace with Avg with Min + Your calculations and see what you get.

[ebolbol]

I would probably use an IIF() function.

the syntax for what you suggested is

IIf(expr, truepart, falsepart)

How would I apply that to

((([TimeOut]-[TimeIn])*24)-[LessMeal],8)

?

[Welshgasman]

the syntax for what you suggested is

IIf(expr, truepart, falsepart)

How would I apply that to

((([TimeOut]-[TimeIn])*24)-[LessMeal],8)

?

How does Min() do it?

IIF(((TimeOut-TimeIn)*24)-LessMeal < 8,((TimeOut-TimeIn)*24)-LessMeal,8)

[ebolbol]

How does Min() do it?

IIF(((TimeOut-TimeIn)*24)-LessMeal < 8,((TimeOut-TimeIn)*24)-LessMeal,8)

Dear Welshgasman,


I'm not at your level, so forgive the elementary questions. Having said that, I believe you just solved my problem and I'm very grateful to you for that! Two thumbs up to you!!!


Best regards,

Ed

[Welshgasman]

You could even create your own function, perhaps called MyMin and pass the two values and return the lowest with a simple if, if that is easier for you? That would emulate the excel min function.

[Welshgasman]

You could even create your own function, perhaps called MyMin and pass the two values and return the lowest with a simple if, if that is easier for you? That would emulate the excel min function.

In fact Vlad (@Gicu) has done it for you with Min2, so you can have exactly as per the Excel function.
Check out Utility Functions at http://forestbyte.com/vba-code-samples/

There is that function and a lot more.

[ebolbol]

In fact Vlad (@Gicu) has done it for you with Min2, so you can have exactly as per the Excel function.
Check out Utility Functions at http://forestbyte.com/vba-code-samples/

There is that function and a lot more.

Excellent! Welshgasman.... can't thank you enough!

[Welshgasman]

Excellent! Welshgasman.... can't thank you enough!

Thank @Gicu,
He created them.

[ebolbol]

Thank @Gicu,
He created them.

Thank you @Gicu!

[Gicu]

You're very welcome !