If statement help

[MikeB]

I need to write an If statement and need some help as this one has me lost.

I have four fields (yearly sales totals), and I want to only select those
records that have had sales in at least three years of the four years.
Sounds simple I just can't get it.

Examples
------------------------------
SalesField1=5
SalesField2=0
SalesField3=0
SalesField4=1
This record would not show as only sales in two of the four years.

SalesField1=5
SalesField2=2
SalesField3=0
SalesField4=6
This record would show as it does have sales in at least three of the four
years.

Any help anyone could give would greatly appreciated.

Thanks,
MikeB

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT [Your Table].*
WHERE
Sgn([Your Table].[SalesField1])
+
Sgn([Your Table].[SalesField2])
+
Sgn([Your Table].[SalesField3])
+
Sgn([Your Table].[SalesField4])

= 3

This assumes your table is named "Your Table" and the values of the sales
fields are not null and greater than or equal to zero.

The Sgn function returns a number indicating the sign of a number. For
numbers greater than zero, Sgn returns 1, for numbers less than zero, Sgn
returns -1, and for zero, Sgn returns zero.