How do I omit zero results from the Min calc on a form

G

Guest

I have a form based on a query that pulls in data from production machines.

In a section footer of this form, I have the calcs for Max, Min, Avg and
StDev.

All Calcs omit the zero results except the Min. (of course)

I would like to select the next higher number due to zero result being data
that must not be used.

I am just using basics: ie... =Min([Field Name])

I have an IIF function setup: ie... =IIf(Min([Field Name])=0,999,Min([Field
Name]))

"999" would be replaced with the correct statement, or another suggerstion?!!?

Thank you in advance.
 
G

Guest

Klatuu,

I made a mistake when I stated "Form". I am actually working in a report.
the report is based off of a "Query". The "Raw Data" is linked to the Access
interface (Report). The query setup is as follows:

SELECT qryRawData110.Logged, qryRawData110.Shot, qryRawData110.TypeMode,
qryRawData110.Heater_1, qryRawData110.Heater_2, qryRawData110.Heater_3,
qryRawData110.Heater_4, qryRawData110.Heater_5, qryRawData110.Hopper,
qryRawData110.Oil_Temp, qryRawData110.Mold_Press_Pos,
qryRawData110.Cushion_Pos, qryRawData110.ChargeFinish_Pos,
qryRawData110.OpenFinish_Pos, qryRawData110.Screw_Rev_1,
qryRawData110.First_Pressure, qryRawData110.Second_Pressure,
qryRawData110.Back_Press_1, qryRawData110.Second_Speed,
qryRawData110.First_Speed_Press, qryRawData110.Second_Speed_Press,
qryRawData110.Pressure_1, qryRawData110.Pressure_2, qryRawData110.Pressure_3,
qryRawData110.Pressure_4, qryRawData110.Rise_up_Time,
qryRawData110.Cycle_Time, qryRawData110.First_Injection_Time,
qryRawData110.Charging_Time, qryRawData110.Pressure_5, qryRawData110.Speed_5,
qryRawData110.Pressure_6, qryRawData110.Mini_Cushion_Pos,
qryRawData110.Speed_8, qryRawData110.First_Plastic_Pressure FROM
qryRawData110 WHERE (((qryRawData110.TypeMode)="Data"));

I apologize for the mistake,

Be-Slow


Klatuu said:
=DMin("[Field Name]", "MyTableName", "[Field Name] > 0]")

Be-Slow said:
I have a form based on a query that pulls in data from production machines.

In a section footer of this form, I have the calcs for Max, Min, Avg and
StDev.

All Calcs omit the zero results except the Min. (of course)

I would like to select the next higher number due to zero result being data
that must not be used.

I am just using basics: ie... =Min([Field Name])

I have an IIF function setup: ie... =IIf(Min([Field Name])=0,999,Min([Field
Name]))

"999" would be replaced with the correct statement, or another suggerstion?!!?

Thank you in advance.
Click to expand...
Click to expand...
 
G

Guest

Okay, I dinked around a little, and came up with this for the control source
of the text box in the section footer:

=Min(IIf([Field Name]>0,[Field Name],Null))

Why this works is that Null values are not considered in aggrate functions.
So anything 0 or less in ignored.

Be-Slow said:
Klatuu,

I made a mistake when I stated "Form". I am actually working in a report.
the report is based off of a "Query". The "Raw Data" is linked to the Access
interface (Report). The query setup is as follows:

SELECT qryRawData110.Logged, qryRawData110.Shot, qryRawData110.TypeMode,
qryRawData110.Heater_1, qryRawData110.Heater_2, qryRawData110.Heater_3,
qryRawData110.Heater_4, qryRawData110.Heater_5, qryRawData110.Hopper,
qryRawData110.Oil_Temp, qryRawData110.Mold_Press_Pos,
qryRawData110.Cushion_Pos, qryRawData110.ChargeFinish_Pos,
qryRawData110.OpenFinish_Pos, qryRawData110.Screw_Rev_1,
qryRawData110.First_Pressure, qryRawData110.Second_Pressure,
qryRawData110.Back_Press_1, qryRawData110.Second_Speed,
qryRawData110.First_Speed_Press, qryRawData110.Second_Speed_Press,
qryRawData110.Pressure_1, qryRawData110.Pressure_2, qryRawData110.Pressure_3,
qryRawData110.Pressure_4, qryRawData110.Rise_up_Time,
qryRawData110.Cycle_Time, qryRawData110.First_Injection_Time,
qryRawData110.Charging_Time, qryRawData110.Pressure_5, qryRawData110.Speed_5,
qryRawData110.Pressure_6, qryRawData110.Mini_Cushion_Pos,
qryRawData110.Speed_8, qryRawData110.First_Plastic_Pressure FROM
qryRawData110 WHERE (((qryRawData110.TypeMode)="Data"));

I apologize for the mistake,

Be-Slow


Klatuu said:
=DMin("[Field Name]", "MyTableName", "[Field Name] > 0]")

Be-Slow said:
I have a form based on a query that pulls in data from production machines.

In a section footer of this form, I have the calcs for Max, Min, Avg and
StDev.

All Calcs omit the zero results except the Min. (of course)

I would like to select the next higher number due to zero result being data
that must not be used.

I am just using basics: ie... =Min([Field Name])

I have an IIF function setup: ie... =IIf(Min([Field Name])=0,999,Min([Field
Name]))

"999" would be replaced with the correct statement, or another suggerstion?!!?

Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Great Score Klatuu!

I did not think to place the "IIF" statement within the "Min"
statement. This fix relieves me from this time consuming issue!

Thank you so much!

Klatuu said:
Okay, I dinked around a little, and came up with this for the control source
of the text box in the section footer:

=Min(IIf([Field Name]>0,[Field Name],Null))

Why this works is that Null values are not considered in aggrate functions.
So anything 0 or less in ignored.

Be-Slow said:
Klatuu,

I made a mistake when I stated "Form". I am actually working in a report.
the report is based off of a "Query". The "Raw Data" is linked to the Access
interface (Report). The query setup is as follows:

SELECT qryRawData110.Logged, qryRawData110.Shot, qryRawData110.TypeMode,
qryRawData110.Heater_1, qryRawData110.Heater_2, qryRawData110.Heater_3,
qryRawData110.Heater_4, qryRawData110.Heater_5, qryRawData110.Hopper,
qryRawData110.Oil_Temp, qryRawData110.Mold_Press_Pos,
qryRawData110.Cushion_Pos, qryRawData110.ChargeFinish_Pos,
qryRawData110.OpenFinish_Pos, qryRawData110.Screw_Rev_1,
qryRawData110.First_Pressure, qryRawData110.Second_Pressure,
qryRawData110.Back_Press_1, qryRawData110.Second_Speed,
qryRawData110.First_Speed_Press, qryRawData110.Second_Speed_Press,
qryRawData110.Pressure_1, qryRawData110.Pressure_2, qryRawData110.Pressure_3,
qryRawData110.Pressure_4, qryRawData110.Rise_up_Time,
qryRawData110.Cycle_Time, qryRawData110.First_Injection_Time,
qryRawData110.Charging_Time, qryRawData110.Pressure_5, qryRawData110.Speed_5,
qryRawData110.Pressure_6, qryRawData110.Mini_Cushion_Pos,
qryRawData110.Speed_8, qryRawData110.First_Plastic_Pressure FROM
qryRawData110 WHERE (((qryRawData110.TypeMode)="Data"));

I apologize for the mistake,

Be-Slow


Klatuu said:
=DMin("[Field Name]", "MyTableName", "[Field Name] > 0]")

:

I have a form based on a query that pulls in data from production machines.

In a section footer of this form, I have the calcs for Max, Min, Avg and
StDev.

All Calcs omit the zero results except the Min. (of course)

I would like to select the next higher number due to zero result being data
that must not be used.

I am just using basics: ie... =Min([Field Name])

I have an IIF function setup: ie... =IIf(Min([Field Name])=0,999,Min([Field
Name]))

"999" would be replaced with the correct statement, or another suggerstion?!!?

Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Klatuu,

I altered the statement in order to keep the negative results in certian
fields to read as follows:

=Min(IIf([Field Name]<>0,[Field Name],Null))

Again thank you for your timeand effort.

Klatuu said:
Okay, I dinked around a little, and came up with this for the control source
of the text box in the section footer:

=Min(IIf([Field Name]>0,[Field Name],Null))

Why this works is that Null values are not considered in aggrate functions.
So anything 0 or less in ignored.

Be-Slow said:
Klatuu,

I made a mistake when I stated "Form". I am actually working in a report.
the report is based off of a "Query". The "Raw Data" is linked to the Access
interface (Report). The query setup is as follows:

SELECT qryRawData110.Logged, qryRawData110.Shot, qryRawData110.TypeMode,
qryRawData110.Heater_1, qryRawData110.Heater_2, qryRawData110.Heater_3,
qryRawData110.Heater_4, qryRawData110.Heater_5, qryRawData110.Hopper,
qryRawData110.Oil_Temp, qryRawData110.Mold_Press_Pos,
qryRawData110.Cushion_Pos, qryRawData110.ChargeFinish_Pos,
qryRawData110.OpenFinish_Pos, qryRawData110.Screw_Rev_1,
qryRawData110.First_Pressure, qryRawData110.Second_Pressure,
qryRawData110.Back_Press_1, qryRawData110.Second_Speed,
qryRawData110.First_Speed_Press, qryRawData110.Second_Speed_Press,
qryRawData110.Pressure_1, qryRawData110.Pressure_2, qryRawData110.Pressure_3,
qryRawData110.Pressure_4, qryRawData110.Rise_up_Time,
qryRawData110.Cycle_Time, qryRawData110.First_Injection_Time,
qryRawData110.Charging_Time, qryRawData110.Pressure_5, qryRawData110.Speed_5,
qryRawData110.Pressure_6, qryRawData110.Mini_Cushion_Pos,
qryRawData110.Speed_8, qryRawData110.First_Plastic_Pressure FROM
qryRawData110 WHERE (((qryRawData110.TypeMode)="Data"));

I apologize for the mistake,

Be-Slow


Klatuu said:
=DMin("[Field Name]", "MyTableName", "[Field Name] > 0]")

:

I have a form based on a query that pulls in data from production machines.

In a section footer of this form, I have the calcs for Max, Min, Avg and
StDev.

All Calcs omit the zero results except the Min. (of course)

I would like to select the next higher number due to zero result being data
that must not be used.

I am just using basics: ie... =Min([Field Name])

I have an IIF function setup: ie... =IIf(Min([Field Name])=0,999,Min([Field
Name]))

"999" would be replaced with the correct statement, or another suggerstion?!!?

Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

My pleasure. It gave me a chance to learn something. I did think of the
negative issue, but you did not mention that so I did not suggest it.

Be-Slow said:
Klatuu,

I altered the statement in order to keep the negative results in certian
fields to read as follows:

=Min(IIf([Field Name]<>0,[Field Name],Null))

Again thank you for your timeand effort.

Klatuu said:
Okay, I dinked around a little, and came up with this for the control source
of the text box in the section footer:

=Min(IIf([Field Name]>0,[Field Name],Null))

Why this works is that Null values are not considered in aggrate functions.
So anything 0 or less in ignored.

Be-Slow said:
Klatuu,

I made a mistake when I stated "Form". I am actually working in a report.
the report is based off of a "Query". The "Raw Data" is linked to the Access
interface (Report). The query setup is as follows:

SELECT qryRawData110.Logged, qryRawData110.Shot, qryRawData110.TypeMode,
qryRawData110.Heater_1, qryRawData110.Heater_2, qryRawData110.Heater_3,
qryRawData110.Heater_4, qryRawData110.Heater_5, qryRawData110.Hopper,
qryRawData110.Oil_Temp, qryRawData110.Mold_Press_Pos,
qryRawData110.Cushion_Pos, qryRawData110.ChargeFinish_Pos,
qryRawData110.OpenFinish_Pos, qryRawData110.Screw_Rev_1,
qryRawData110.First_Pressure, qryRawData110.Second_Pressure,
qryRawData110.Back_Press_1, qryRawData110.Second_Speed,
qryRawData110.First_Speed_Press, qryRawData110.Second_Speed_Press,
qryRawData110.Pressure_1, qryRawData110.Pressure_2, qryRawData110.Pressure_3,
qryRawData110.Pressure_4, qryRawData110.Rise_up_Time,
qryRawData110.Cycle_Time, qryRawData110.First_Injection_Time,
qryRawData110.Charging_Time, qryRawData110.Pressure_5, qryRawData110.Speed_5,
qryRawData110.Pressure_6, qryRawData110.Mini_Cushion_Pos,
qryRawData110.Speed_8, qryRawData110.First_Plastic_Pressure FROM
qryRawData110 WHERE (((qryRawData110.TypeMode)="Data"));

I apologize for the mistake,

Be-Slow


:

=DMin("[Field Name]", "MyTableName", "[Field Name] > 0]")

:

I have a form based on a query that pulls in data from production machines.

In a section footer of this form, I have the calcs for Max, Min, Avg and
StDev.

All Calcs omit the zero results except the Min. (of course)

I would like to select the next higher number due to zero result being data
that must not be used.

I am just using basics: ie... =Min([Field Name])

I have an IIF function setup: ie... =IIf(Min([Field Name])=0,999,Min([Field
Name]))

"999" would be replaced with the correct statement, or another suggerstion?!!?

Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Klatuu,

You are correct, I did not state I had negative numbers. I just wanted to
include the information in this discussion in case it would help someone else
(FYI). Please don't feel my last entry was directed towards you.

My report is working like a champ thanks to you.

Berry

Klatuu said:
My pleasure. It gave me a chance to learn something. I did think of the
negative issue, but you did not mention that so I did not suggest it.

Be-Slow said:
Klatuu,

I altered the statement in order to keep the negative results in certian
fields to read as follows:

=Min(IIf([Field Name]<>0,[Field Name],Null))

Again thank you for your timeand effort.

Klatuu said:
Okay, I dinked around a little, and came up with this for the control source
of the text box in the section footer:

=Min(IIf([Field Name]>0,[Field Name],Null))

Why this works is that Null values are not considered in aggrate functions.
So anything 0 or less in ignored.

:

Klatuu,

I made a mistake when I stated "Form". I am actually working in a report.
the report is based off of a "Query". The "Raw Data" is linked to the Access
interface (Report). The query setup is as follows:

SELECT qryRawData110.Logged, qryRawData110.Shot, qryRawData110.TypeMode,
qryRawData110.Heater_1, qryRawData110.Heater_2, qryRawData110.Heater_3,
qryRawData110.Heater_4, qryRawData110.Heater_5, qryRawData110.Hopper,
qryRawData110.Oil_Temp, qryRawData110.Mold_Press_Pos,
qryRawData110.Cushion_Pos, qryRawData110.ChargeFinish_Pos,
qryRawData110.OpenFinish_Pos, qryRawData110.Screw_Rev_1,
qryRawData110.First_Pressure, qryRawData110.Second_Pressure,
qryRawData110.Back_Press_1, qryRawData110.Second_Speed,
qryRawData110.First_Speed_Press, qryRawData110.Second_Speed_Press,
qryRawData110.Pressure_1, qryRawData110.Pressure_2, qryRawData110.Pressure_3,
qryRawData110.Pressure_4, qryRawData110.Rise_up_Time,
qryRawData110.Cycle_Time, qryRawData110.First_Injection_Time,
qryRawData110.Charging_Time, qryRawData110.Pressure_5, qryRawData110.Speed_5,
qryRawData110.Pressure_6, qryRawData110.Mini_Cushion_Pos,
qryRawData110.Speed_8, qryRawData110.First_Plastic_Pressure FROM
qryRawData110 WHERE (((qryRawData110.TypeMode)="Data"));

I apologize for the mistake,

Be-Slow


:

=DMin("[Field Name]", "MyTableName", "[Field Name] > 0]")

:

I have a form based on a query that pulls in data from production machines.

In a section footer of this form, I have the calcs for Max, Min, Avg and
StDev.

All Calcs omit the zero results except the Min. (of course)

I would like to select the next higher number due to zero result being data
that must not be used.

I am just using basics: ie... =Min([Field Name])

I have an IIF function setup: ie... =IIf(Min([Field Name])=0,999,Min([Field
Name]))

"999" would be replaced with the correct statement, or another suggerstion?!!?

Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum() in Form footer (again) 5
Ignore zero values with the Min function on a form 3
Using IIf to return a zero value in a text box 1
Using DSUM to get running total on a filtered form 0
How do I populate a subform field with a value from the main form? 4
Using the results of a query over more than one field in a form 3
How to create a calculated field formula based on Pivot Table resu 1
form entry results in results being pulled into the form 6

Top