Expression used to calculate previous ytd count

[Bryan]

I want to compare YTD totals for '06 & '05. The expression below produces my
current YTD totals based on a date entered:
Cur YTD: Sum(IIf(Year([cases].[date])=Year([Enter End Date]) And
[cases].[date]<=[Enter End Date],[count],0))

I can't seem to modify this to reflect the previous years YTD totals.

PreYTD: Sum(IIf(Year([cases].[date])=Year([Enter End Date])-1 And
[cases].[date]<=([enter end date]-365),[count],0))

 

[Dale Fye]

Bryan,

I assume you are trying to do this in a single query. First off, I would
encourage you to not give fields names like [Date]. "Date" is a reservered
word, and the name of an Access function. When you use it as the name of a
field, you set yourself up for problems.

I've formatted this to make it easier for me to read in this window, but I
believe you could use:
PrevYTD: SUM(iif([Cases].[Date] > dateserial(Year([Enter End Date])-1, 1, 1)
AND
[Cases].[Date] <= dateserial(Year([Enter End
Date])-1, _

Month([Enter End Date]), _

Day([Enter End Date]))), _
[Count], 0)

Keep in mind that if your Cases.Date field contains times as well as date
values, this query would not include the data for the day you enter to the
prompt [Enter End Date].

HTH
Dale

 

[Bryan]

Excellent! thank you.

Bryan,

I assume you are trying to do this in a single query. First off, I would
encourage you to not give fields names like [Date]. "Date" is a reservered
word, and the name of an Access function. When you use it as the name of a
field, you set yourself up for problems.

I've formatted this to make it easier for me to read in this window, but I
believe you could use:
PrevYTD: SUM(iif([Cases].[Date] > dateserial(Year([Enter End Date])-1, 1, 1)
AND
[Cases].[Date] <= dateserial(Year([Enter End
Date])-1, _

Month([Enter End Date]), _

Day([Enter End Date]))), _
[Count], 0)

Keep in mind that if your Cases.Date field contains times as well as date
values, this query would not include the data for the day you enter to the
prompt [Enter End Date].

HTH
Dale

I want to compare YTD totals for '06 & '05. The expression below produces my
current YTD totals based on a date entered:
Cur YTD: Sum(IIf(Year([cases].[date])=Year([Enter End Date]) And
[cases].[date]<=[Enter End Date],[count],0))

I can't seem to modify this to reflect the previous years YTD totals.

PreYTD: Sum(IIf(Year([cases].[date])=Year([Enter End Date])-1 And
[cases].[date]<=([enter end date]-365),[count],0))

 

[Dale Fye]

You're welcome.

Excellent! thank you.

Bryan,

I assume you are trying to do this in a single query. First off, I would
encourage you to not give fields names like [Date]. "Date" is a
reservered
word, and the name of an Access function. When you use it as the name
of a
field, you set yourself up for problems.

I've formatted this to make it easier for me to read in this window, but
I
believe you could use:
PrevYTD: SUM(iif([Cases].[Date] > dateserial(Year([Enter End Date])-1, 1,
1)
AND
[Cases].[Date] <= dateserial(Year([Enter End
Date])-1, _

Month([Enter End Date]), _

Day([Enter End Date]))), _
[Count], 0)

Keep in mind that if your Cases.Date field contains times as well as date
values, this query would not include the data for the day you enter to
the
prompt [Enter End Date].

HTH
Dale

I want to compare YTD totals for '06 & '05. The expression below
produces my
current YTD totals based on a date entered:
Cur YTD: Sum(IIf(Year([cases].[date])=Year([Enter End Date]) And
[cases].[date]<=[Enter End Date],[count],0))

I can't seem to modify this to reflect the previous years YTD totals.

PreYTD: Sum(IIf(Year([cases].[date])=Year([Enter End Date])-1 And
[cases].[date]<=([enter end date]-365),[count],0))

 

[chickalina]

Could you use the same expression to figure out the following:
I have a report which prompts the user ( an [Enter Date] field in the query)
to pick the quarter they want to view. There are 20 fields that need to have
that quarter information displayed BUT, also needed is the balance prior to
the quarter chosen and then a total of the prior balance and the current
quarter.
I hope this makes sense...

tbl_ReserveSchedule
IssueID
ChangeDate
Quarter: Format .... with [Enter Quarter] in the Field: to enter the quarter
Field1Tax
Field1A: an IIF statement for null values
Field1Interest
Field1B: an IIF statement for null values

I hope someone can help.... I just need to finish this part of the database
and I am done with it.. I have to finish it ASAP.

You're welcome.

Excellent! thank you.

Bryan,

I assume you are trying to do this in a single query. First off, I would
encourage you to not give fields names like [Date]. "Date" is a
reservered
word, and the name of an Access function. When you use it as the name
of a
field, you set yourself up for problems.

I've formatted this to make it easier for me to read in this window, but
I
believe you could use:
PrevYTD: SUM(iif([Cases].[Date] > dateserial(Year([Enter End Date])-1, 1,
1)
AND
[Cases].[Date] <= dateserial(Year([Enter End
Date])-1, _

Month([Enter End Date]), _

Day([Enter End Date]))), _
[Count], 0)

Keep in mind that if your Cases.Date field contains times as well as date
values, this query would not include the data for the day you enter to
the
prompt [Enter End Date].

HTH
Dale

:

I want to compare YTD totals for '06 & '05. The expression below
produces my
current YTD totals based on a date entered:
Cur YTD: Sum(IIf(Year([cases].[date])=Year([Enter End Date]) And
[cases].[date]<=[Enter End Date],[count],0))

I can't seem to modify this to reflect the previous years YTD totals.

PreYTD: Sum(IIf(Year([cases].[date])=Year([Enter End Date])-1 And
[cases].[date]<=([enter end date]-365),[count],0))