Date Range Calculation

[lake_shore]

I want to calculate quarters for a series of date ranges. The following does not work. All records are reported as None for the field. The orginal field "Date" is a date field. Any suggestions?

Case(

Date >= 09/2/2003 & Date <= 10/31/2003, "Q1",

Date >= 11/01/2003 & Date <= 01/16/2004, "Q2",

Date >= 01/20/2004 & Date <= 04/02/2004, "Q3",

Date >= 04/05/2004 & Date <= 6/25/2004, "Q4", "None")

[LaRetta]

Hi lake_shore,

The '&' is used in text calculations. And I also think you must de-construct the date and re-construct it again but I may be wrong. And <= etc applies to a number (or date) field. Try:

Case(DateField >= Date(9,2,2003) and DateField <= Date(10,31,2003), "Q1",

DateField >= Date(11,1,2003) and DateField<= Date(1,16,2004), "Q2",

DateField >= Date(1,20,2004) and DateField<= Date(4,2,2004), "Q3",

DateField >= Date(4,5,2004) and DateField<= Date(6,25,2004), "Q4", "None")

I changed the field name to DateField to separate it from the Date function. I never name a field Date because of the confusion it can cause. It should be ActivityDate or somesuch name.

LaRetta

[lake_shore]

Thank you. Worked perfectly. I appreciate your quick reply.