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

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top