>What's wrong with my SELECT statement below?
>
>Error I get - "Command contains unrecognized phrase/keyword."
>
>I am trying to select records for a certain year that have a certain ID code and count/list them by month for assorted zip codes.
>
>cField = cRetVal
>
>SELECT mytable.zip, mytable.date ;
> FROM mytable ;
> WHERE YEAR(mytable.date) = YEAR(DATE())-1 .AND. ;
> WHERE mytable.&cField = "1A" .OR. ;
> mytable.&cField = "1B" .OR. ;
> mytable.&cField = "1C" .OR. ;
> ORDER BY MONTH(mytable.date) ;
> INTO CURSOR rpt
>
>Thanks for helping me out.

First, the WHERE verb should only appear once in the SELECT statement. This is the principal problem. I'd write the whole thing differently; this is not saying that your basic approach is wrong, just that I'd do it differently. I'd minimze the number of times that functions were evaluated, and use a different approach to membership of values in a set:

nPriorYear = YEAR(DATE()) - 1
SELECT Zip, Date ;
  FROM MyTable ;
 WHERE Year(Date) = nPriorYear AND ;
       &cField IN ("1A","1B","1C") ;
 ORDER BY Date ;
 INTO CURSOR rpt

Now the why. By precomputing the value nPriorYear, the number you want to use in your comparison is computed once and the value is referenced by each record examined by the SELECT; in your code, the static value YEAR(DATE())-1 is recomputed for each record.

The IN clause is both more readable, and actually does the comparison faster than three separate explicit compares. If you wanted to built the list programmatically, you could do this:

cListToCompare = '"1A","1B","1C"'

and then the IN clause would read:

&cField IN (&cListToCompare)

Since all of the result set is within a single year, you may as well sort the result set by the Date field - using the field value itself achieves the same basic ordering in this case, without the overhead of the function call again. More complex ordering conditions might require the use of the function, but where it isn't needed, it just adds more overhead for no change in result.

There are lots of ways to skin this particular cat; the example I gave is one of many possible approaches. The selection in the IN clause could be written several different ways by itself; your approach of using OR to string compares together, using the $ (substring search) operator, or any of a number of string comparing functions like INLIST() and AT() all could be used to accomplish the same thing.

hth,

Ed