Finding Minimum but not "0" values

[CJBast]

I have a query where I'm finding the minimum of a field.
Many of the records for that field will have '0' (zero) so
when the query runs, that min shows as 0 and I would like
it to be the next number up from that say, 23. Any ideas
on how to fix this?

~cj

 

[Ken Snell]

SELECT Min([FieldName])
FROM TableName
WHERE [FieldName] > 0;

 

[Guest]

Ken~

Thanks. I tried that but it removes the whole record. I
have a series of scores and not everyone participates.
I'd like to know the lowest score of those who do. Ex:
The lowest would be 33, 42, 39.

R1 = 33, 42, 0
R2 = 44, 43, 39
R3 = 0, 0, 42

~cj

-----Original Message-----
SELECT Min([FieldName])
FROM TableName
WHERE [FieldName] > 0;


--

Ken Snell
<MS ACCESS MVP>

I have a query where I'm finding the minimum of a field.
Many of the records for that field will have '0' (zero) so
when the query runs, that min shows as 0 and I would like
it to be the next number up from that say, 23. Any ideas
on how to fix this?

~cj

.

 

[Duane Hookom]

Select Min(IIF(Field=0,100000000,[Field]))

--
Duane Hookom
MS Access MVP

Ken~

Thanks. I tried that but it removes the whole record. I
have a series of scores and not everyone participates.
I'd like to know the lowest score of those who do. Ex:
The lowest would be 33, 42, 39.

R1 = 33, 42, 0
R2 = 44, 43, 39
R3 = 0, 0, 42

~cj

-----Original Message-----
SELECT Min([FieldName])
FROM TableName
WHERE [FieldName] > 0;


--

Ken Snell
<MS ACCESS MVP>

I have a query where I'm finding the minimum of a field.
Many of the records for that field will have '0' (zero) so
when the query runs, that min shows as 0 and I would like
it to be the next number up from that say, 23. Any ideas
on how to fix this?

~cj

.

 

[CJBast]

Duane~

Yee Ha! I finally found an article on MS "ACC2000: How to
Exclude Zero Values When You Calculate Averages" that I
thought I could get to work. But your solution is much
simpler.

Thank you.
~cj

-----Original Message-----
Select Min(IIF(Field=0,100000000,[Field]))

--
Duane Hookom
MS Access MVP

Ken~

Thanks. I tried that but it removes the whole record. I
have a series of scores and not everyone participates.
I'd like to know the lowest score of those who do. Ex:
The lowest would be 33, 42, 39.

R1 = 33, 42, 0
R2 = 44, 43, 39
R3 = 0, 0, 42

~cj

-----Original Message-----
SELECT Min([FieldName])
FROM TableName
WHERE [FieldName] > 0;


--

Ken Snell
<MS ACCESS MVP>

I have a query where I'm finding the minimum of a field.
Many of the records for that field will have '0'

(zero)
so

when the query runs, that min shows as 0 and I would like
it to be the next number up from that say, 23. Any ideas
on how to fix this?

~cj


.

.

 

[Guest]

Duane~

I just gave it a try and the low score now becomes 1
billion. I'll play with a bit...

~cj

-----Original Message-----
Select Min(IIF(Field=0,100000000,[Field]))

--
Duane Hookom
MS Access MVP

Ken~

Thanks. I tried that but it removes the whole record. I
have a series of scores and not everyone participates.
I'd like to know the lowest score of those who do. Ex:
The lowest would be 33, 42, 39.

R1 = 33, 42, 0
R2 = 44, 43, 39
R3 = 0, 0, 42

~cj

-----Original Message-----
SELECT Min([FieldName])
FROM TableName
WHERE [FieldName] > 0;


--

Ken Snell
<MS ACCESS MVP>

I have a query where I'm finding the minimum of a field.
Many of the records for that field will have '0'

(zero)
so

when the query runs, that min shows as 0 and I would like
it to be the next number up from that say, 23. Any ideas
on how to fix this?

~cj


.

.

 

[CJBast]

Duane~

It did work. My appologies.

Two things you don't do just before going to bed (and
apparently just upon waking):

1 - Delete ANYTHING
2 - Compare numbers in columns.

Thanks again.
~cj

-----Original Message-----
Select Min(IIF(Field=0,100000000,[Field]))

--
Duane Hookom
MS Access MVP

Ken~

Thanks. I tried that but it removes the whole record. I
have a series of scores and not everyone participates.
I'd like to know the lowest score of those who do. Ex:
The lowest would be 33, 42, 39.

R1 = 33, 42, 0
R2 = 44, 43, 39
R3 = 0, 0, 42

~cj

-----Original Message-----
SELECT Min([FieldName])
FROM TableName
WHERE [FieldName] > 0;


--

Ken Snell
<MS ACCESS MVP>

I have a query where I'm finding the minimum of a field.
Many of the records for that field will have '0'

(zero)
so

when the query runs, that min shows as 0 and I would like
it to be the next number up from that say, 23. Any ideas
on how to fix this?

~cj


.

.