Calculating Dates

[kritter0021]

Ok, If I want to calculate the difference of a date with the following date
in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value
in B1 of 35. How do I do this? Thanks!

In Excell the formula looks like this:

=IF(+A1-A2>0,A1-A2,0)

 

[Dorian]

IIF(Datediff('d',A2,A1)>0,Datediff('d',A2,A1),0)
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[kritter0021]

That is just giving me zeros. Here is some data which may help. Thanks!

Here is the data that my query looks like and Dif Date is what I want
calculated. I think I need to number each record. How do I do that, so just
1,2,3,4.... Once I have them numbered then it can look at previous record.
I don't know. Maybe...Thanks.

Product Date Dif Date
A 1/23/2009 40
A 3/4/2009 65
A 5/8/2009 0
B 3/2/2009 28
B 3/30/2009 0

Text Date Number

I am wanting to calculate the Diff Date and when it moves to a new product
display the average of of the product, so instead of displaying 0 show the
average of A of 52.5. Not sure if this is possible. Real easy in excell.

 

[kritter0021]

That does work when you are calculating the difference between two fields,
but I need the calculation for the next date in the same field.

 

[John W. Vinson]

That does work when you are calculating the difference between two fields,
but I need the calculation for the next date in the same field.

Your problem is that in Excel it's very normal to refer to "the next row" or
"the previous row".

In Access, there IS no "next row".

Records in an Access Table are unordered: think of a table as a bucket full of
eggs, where each egg is a record. There is no predefined order of records. You
must - no option! - have some field or fields within the table to define the
order. AT A GUESS (it's not clear to me from your example) you want to find
the number of days between the date for a given record, and the most recent
prior date for the same company - if there is one. Of so, you'll need a
Subquery: something like

SELECT A.Product, A.[Date], DateDiff("d", .[Date], [A].[Date])
FROM yourtable AS A
INNER JOIN yourtable AS B
ON A.Product = B.Product
WHERE B.[Date] = (SELECT Max(C.[Date]) FROM yourtable AS C WHERE C.[Date] <
[A].[Date]);

Note that Date is a reserved word (for the builtin Date() function which
returns the system clock date); I'd really suggest changing the fieldname.