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

 

[Van T. Dinh]

Create a Calculated Field in your Query grid:

NonZeroSales: Abs((SalesField1>0.0) + (SalesField2>0.0) +
(SalesField3>0.0) + (SalesField4>0.0))

and in the criteria row of this Calculated Field, use:

= 3

 

[John Spencer (MVP)]

Add a calculated column to your query that checks to see if the Salesfield has a
value greater than 0 and then add that up and check if the total is greater than
2.

For example:

Field: NumYears:Abs(SalesField1>0 + Salesfield2>0 + SalesField3>0 +
SalesField4>0)
Criteria: >2

The calculation will probably wrap in the newsreader. Just get it all on one
line.

 

[Lynn Trapp]

Not only is it not simple, but it is virtually impossible as you have your
data structured. Having your yearly sales totals in separate fields works
great in a spreadsheet application, like Excel, but it is a nightmare for a
relational database like Access.

You should have a field for the Yearly Total AND a field for the Year. Then
you can work with the data in a query to get the result you want.

 

[MikeB]

Worked great.
Thanks very much.

MikeB

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Van T. Dinh]

This is a temporary fix, though.

I agrre with Lynn's assessment that the Table should be re-
structured.

HTH
Van T. Dinh
MVP (Access)