DSUM Problem With Dates

[Paul Fenton]

Thanks to Ken and Van, this works...

DSum("[jobPrice]","tClients","Month([Date Sold])=1 And Year([Date
Sold])=2005")

to give me the total for January, this year. And I can get it for any
other month/year combination.

I want to make it more general and not specify the exact year, but
rather to make it work for "current year" or some # of years prior to
the current year. So I've been trying to make the statement work by
using Now() or Date() and Now()-1, Now()-2, etc. but I keep getting no
result or "Null" or syntax errors.

So how would I make it work using Now() instead of "2005" and Now()-1
instead of "2004"?


Paul Fenton
(e-mail address removed)

 

[Joseph Meehan]

Thanks to Ken and Van, this works...

DSum("[jobPrice]","tClients","Month([Date Sold])=1 And Year([Date
Sold])=2005")

to give me the total for January, this year. And I can get it for any
other month/year combination.

I want to make it more general and not specify the exact year, but
rather to make it work for "current year" or some # of years prior to
the current year. So I've been trying to make the statement work by
using Now() or Date() and Now()-1, Now()-2, etc. but I keep getting no
result or "Null" or syntax errors.

So how would I make it work using Now() instead of "2005" and Now()-1
instead of "2004"?

The idea should work, but you will need to extract just the year portion
of the Now expression.

 

[fredg]

Thanks to Ken and Van, this works...

DSum("[jobPrice]","tClients","Month([Date Sold])=1 And Year([Date
Sold])=2005")

to give me the total for January, this year. And I can get it for any
other month/year combination.

I want to make it more general and not specify the exact year, but
rather to make it work for "current year" or some # of years prior to
the current year. So I've been trying to make the statement work by
using Now() or Date() and Now()-1, Now()-2, etc. but I keep getting no
result or "Null" or syntax errors.

So how would I make it work using Now() instead of "2005" and Now()-1
instead of "2004"?

Paul Fenton
(e-mail address removed)

Now()-1 is this exactly this time yesterday.
Year(Now())-1 is 1 year ago.

Better would be using date(), which does not have a time value.
For all records of the current year:
DSum("[jobPrice]","tClients","Year([Date Sold])= Year(Date())")

For all records of the previous year:
DSum("[jobPrice]","tClients","Year([Date Sold]) = Year(Date()) - 1")

 

[John Vinson]

So how would I make it work using Now() instead of "2005" and Now()-1
instead of "2004"?

Now() returns the current system clock time to a few microseconds...
overkill!

Use

Year(Date())

instead of 2005.

John W. Vinson[MVP]

 

[Paul Fenton]

Thank you Fred, that did the trick.


Paul Fenton

Thanks to Ken and Van, this works...

DSum("[jobPrice]","tClients","Month([Date Sold])=1 And Year([Date
Sold])=2005")

to give me the total for January, this year. And I can get it for any
other month/year combination.

I want to make it more general and not specify the exact year, but
rather to make it work for "current year" or some # of years prior to
the current year. So I've been trying to make the statement work by
using Now() or Date() and Now()-1, Now()-2, etc. but I keep getting no
result or "Null" or syntax errors.

So how would I make it work using Now() instead of "2005" and Now()-1
instead of "2004"?

Paul Fenton
(e-mail address removed)

Now()-1 is this exactly this time yesterday.
Year(Now())-1 is 1 year ago.

Better would be using date(), which does not have a time value.
For all records of the current year:
DSum("[jobPrice]","tClients","Year([Date Sold])= Year(Date())")

For all records of the previous year:
DSum("[jobPrice]","tClients","Year([Date Sold]) = Year(Date()) - 1")

 

[Van T. Dinh]

You can use:

Year(Date())

to return the current year and for last year:

Year(Date()) - 1