I am trying to create a calculated field in a query that will give me seasons. I have a field with dates [DATE] and I want to know if that date falls in Winter, Summer, Spring or Fall. I have the data (Spring begins on MAR 21, Summer begins on JUN 21, Fall begins on SEP 23, and Winter begins on DEC 21) but I am not sure how to put it all together so I can create a season field from the date field. Thanks for your help. Mike

--
Message posted via http://www.accessmonster.com

Michael Winn via AccessMonster.com wrote:

>I am trying to create a calculated field in a query that will give me seasons. I have a field with dates [DATE] and I want to know if that date falls in Winter, Summer, Spring or Fall. I have the data (Spring begins on MAR 21, Summer begins on JUN 21, Fall begins on SEP 23, and Winter begins on DEC 21) but I am not sure how to put it all together so I can create a season field from the date field.


You can calculate the season in a variety of ways. A quick
and dirty method is to use the Switch function:

Switch(Format([Date],"mmdd") < "0321","Winter",
Format([Date],"mmdd") < "0621","Spring",
Format([Date],"mmdd") < "0923","Summer",
Format([Date],"mmdd") < "1221","Fall", True, "Winter")

--
Marsh
MVP [MS Access]

Michael Winn via AccessMonster.com wrote:
> I am trying to create a calculated field in a query that will give me seasons. I have a field with dates [DATE] and I want to know if that date falls in Winter, Summer, Spring or Fall. I have the data (Spring begins on MAR 21, Summer begins on JUN 21, Fall begins on SEP 23, and Winter begins on DEC 21) but I am not sure how to put it all together so I can create a season field from the date field. Thanks for your help. Mike
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access 2002:

Perhaps the DatePart() and Switch() functions. This evaluation will
only work in a query:

SELECT
Switch(DatePart("y",[Date]) Between 81 And 172, "Spring",
DatePart("y",[Date]) Between 173 And 266, "Summer",
DatePart("y",[Date]) Between 267 And 355, "Fall",
DatePart("y",[Date]) Between 356 And 366 OR
DatePart("y",[Date]) Between 1 And 80, "Winter" ) As Season,
....
FROM ... etc.

The "y" interval indicates we want the Day of the Year.

The only problem is leap-year, which is why I put 366 as the ending day
of year. But, it may be better to put a separate Switch() evaluation on
leap years:

If Year([Date]) Mod 4 = 0 Then
... do leap year days
Else
... do non-leap year days
End If


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd7heYechKqOuFEgEQLyFgCfbJkuysAxkM9oGAB/R5NppHOo+2gAoMrD
O9C9N2eJZUv5WJ2qUvL1HGTo
=lM0H
-----END PGP SIGNATURE-----

Thank you very much.

--
Message posted via http://www.accessmonster.com