YTD Subquery is not including December why?

Hi Allen:

I created a query with a subquery to sum the hours worked by period (i.e.,
month) and then YTD hours next to the month hours.

However, it's not entirely working for me and it's driving me crazy.

The query gives me by Dept and period the ytd hours in the field labeled
"YTDHours"

One final glitch remains:

I added one record for December (period 12) and it is "blank" in the
YTDHours column.

Why doesn't it pick up December?

Any help would be greatly appreciated!

if it would be helpful to look at the database I can send it if you send me
an email.
or I can past the code.

On Wed, 25 Nov 2009 18:06:01 -0800, jeromez
wrote:

Hi Allen:

I created a query with a subquery to sum the hours worked by period (i.e.,
month) and then YTD hours next to the month hours.

However, it's not entirely working for me and it's driving me crazy.

The query gives me by Dept and period the ytd hours in the field labeled
"YTDHours"

One final glitch remains:

I added one record for December (period 12) and it is "blank" in the
YTDHours column.

Why doesn't it pick up December?

Any help would be greatly appreciated!

if it would be helpful to look at the database I can send it if you send me
an email.
or I can past the code.

Please post the code (the SQL view of the query) and a description of the
structure of your table. If the table has a separate field for each month
("period"...?) then it's not correctly designed.
--

John W. Vinson [MVP]

one more note:

I'm only using two departments in the example:

1. 405 which is the same as 4051
2. 455 which is the same as 4552

I initially started with 405 and 455 and thought it might be easier to
understand the example if added an additional number to the dept. but didn't
change all the data in the example before I posted and it was permanently
etched in time :P

Actually DateSerial will handle month 13 easily.

DateSerial(2009,13,1) will return January 1, 2010 (the 13th month of 2009 is
the first month of 2010)

It will also handle
DateSerial(2009,13,32) and return February 1, 2010

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Daryl S wrote:
Jeromez -

The following cannot evaluate to a date, since there aren't 13 months in a
year:

A.RepDate DateSerial(Year(Tbl_Hours_Actual.RepDate),
Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours

Try converting it to this:

A.RepDate DateSerial(Year(Tbl_Hours_Actual.RepDate),
Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours

to this:

A.RepDate IF(Month(Tbl_Hours_Actual.RepDate) = 12,
DateSerial(Year(Tbl_Hours_Actual.RepDate)+1,
,1,0),DateSerial(Year(Tbl_Hours_Actual.RepDate),
Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours

(this is untested, but you get the idea...)

Actually DateSerial will handle month 13 easily.

DateSerial(2009,13,1) will return January 1, 2010 (the 13th month of 2009 is
the first month of 2010)

It will also handle
DateSerial(2009,13,32) and return February 1, 2010

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Daryl S wrote:
Jeromez -

The following cannot evaluate to a date, since there aren't 13 months in a
year:

A.RepDate DateSerial(Year(Tbl_Hours_Actual.RepDate),
Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours

Try converting it to this:

A.RepDate DateSerial(Year(Tbl_Hours_Actual.RepDate),
Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours

to this:

A.RepDate IF(Month(Tbl_Hours_Actual.RepDate) = 12,
DateSerial(Year(Tbl_Hours_Actual.RepDate)+1,
,1,0),DateSerial(Year(Tbl_Hours_Actual.RepDate),
Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours

(this is untested, but you get the idea...)

Actually DateSerial will handle month 13 easily.

DateSerial(2009,13,1) will return January 1, 2010 (the 13th month of 2009 is
the first month of 2010)

It will also handle
DateSerial(2009,13,32) and return February 1, 2010

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Daryl S wrote:
Jeromez -

The following cannot evaluate to a date, since there aren't 13 months in a
year:

A.RepDate DateSerial(Year(Tbl_Hours_Actual.RepDate),
Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours

Try converting it to this:

A.RepDate DateSerial(Year(Tbl_Hours_Actual.RepDate),
Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours

to this:

A.RepDate IF(Month(Tbl_Hours_Actual.RepDate) = 12,
DateSerial(Year(Tbl_Hours_Actual.RepDate)+1,
,1,0),DateSerial(Year(Tbl_Hours_Actual.RepDate),
Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours

(this is untested, but you get the idea...)