datetime and sum: type convert?

[Earth]

Hi again,

I have a datetime column which I multiply by another column (ints). I then want to sum this result. However, Access will not allow me to perform this sum, with the following error msg:
"The sum or average aggregate operation cannot take a datetime data type as an argument."

My question is: can I convert the data type of my datetime * int results column (which appears to be of type datetime also) to another type that will allow me to sum?

Thanks.

 

[JoanneM]

Are you wanting to
retrieve a number of days/hrs/mins betwen two dates and multiply them by a given number?
because if that is the case you need to use the datediff function first and this will give you integer to do the calculation.

If you would care to explain further I would be glad to help

regards

Jo

 

[Earth]

Hi Jo,

Yes that was exactly what I was trying to do. I solved the problem by using the DateDiff function as you suggested. Could someone explain why Access:
1. Gives what "appears" to be an integer when you do "Date1-Date2"
2. Is happy to take this "difference" and multiply it by another number: the result of which looks like a normal integer.
3. Balks when you try to do a sum on a column of these result values?

Yet when I change Step 1 to a "DateDiff" it works (as obviously the DateDiff returns a real integer, not just something that looks like one!). But why does Access allow Date1-Date2, or at least, not format the type of results as you would expect?

Thanks Jo!