Incorrect values from a SUM function. Need help please.

[Tbird2340]

I have a table that has a long date field. I want to calculate values off of
that field. In order for my to get the values that I need I have to change
the format from long date (date and time) into shortdate (just mm/dd/yyyy).
To do this I created a query with the following field:

CompletedDateShort: Format([CompletedDate],'mm/dd/yyyy')

I then run a SQL statement on this query in an asp page to produce the
values I want. Here is the SQL statement:

SELECT AssignedTo,
Sum(ABS(CompletedDateShort = Date())) as "Current Day",
Sum(ABS(CompletedDateShort>=Date()-7)) as "Current Week",
Sum(ABS(CompletedDateShort>=Date()-30)) as "Current Month"
FROM HelpDesk_Query
GROUP BY AssignedTo

Now, I get the correct values for the "Current Day" field but I get some
VERY off results for the other two. Any idea on what I am doing wrong?

Thanks so much!

 

[Michel Walsh]

Hi,


You have probably tried using <= ?

sure, you will get "in the last seven days", which, for a Wednesday, is
not the same as "in the same week" (which is only four days, not 7: from
Sunday to Wednesday).



Hoping it may help,
Vanderghast, Access MVP

 

[Duane Hookom]

I'm not sure how Access/ASP handles your date values since you seem to
convert one to a string variant (CompletedDateShort) and the others are
actual dates.

I also agree with Michel Walsh.

 

[Tbird2340]

Sorry... I shouldn't have "Current Week" and "Current Month"... I really
want "Past 7 Days" and "Past 30 Days"

I changed it to try:

SELECT AssignedTo,
Sum(Abs(CompletedDateShort = Date())) as "Current Day",
Sum(ABS(CompletedDateShort<=Date()-7)) as "Past 7 Days",
Sum(Abs(CompletedDateShort<=Date()-30)) as "Past 30 Days"
FROM HelpDesk_Query

And I still get incorrect values?? I don't get it!

Thanks for the reply.

 

[Tbird2340]

Yes, that's what it was.. It didn't like the string variant.. I used the
CompletedDateShort for the first formula (Sum(ABS(CompletedDateShort =
Date())) as "Current Day") because I needed to take the time off the value
to get any results.. I could still use the long date for the other two
formulas though and it returns the correct values.

Thanks for the help.