If statement help

  • Thread starter Thread starter MikeB
  • Start date Start date
M

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
 
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
Click to expand...

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.
 
Back
Top