Calculate Diff Date?

[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)

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.

 

[raskew via AccessMonster.com]

Hi -

It's equally simple in Access. Look up the DateDiff(() function in your help
file.

Best wishes -- Bob

 

[kritter0021]

That works when you are calculating it between two fields, not when you are
trying to use the previous date in the same field. So

Product Date Dif Date
I need DiffDate beween the first two records calculated. Not sure how to
make it do that. 3/4/09 - 1/23/09 = 40.

 

[KARL DEWEY]

Use these three queries --

kritter0021
SELECT YourTable.Product, YourTable.ProdDate, (SELECT COUNT(*) FROM
YourTable AS [XX] WHERE [XX].Product = YourTable.Product AND [XX].ProdDate
<= YourTable.ProdDate) AS RANK
FROM YourTable
ORDER BY YourTable.Product, YourTable.ProdDate;

kritter0021_X
SELECT kritter0021.Product, kritter0021.ProdDate, kritter0021.RANK
FROM kritter0021
UNION ALL SELECT YourTable.Product, NULL AS ProdDate, Max([RANK]) +1 AS RANK1
FROM YourTable INNER JOIN kritter0021 ON YourTable.Product =
kritter0021.Product
GROUP BY YourTable.Product;

SELECT kritter0021_X.Product, kritter0021_X.ProdDate,
IIf([kritter0021_X_1].[ProdDate] Is
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]))
AS Expr1
FROM kritter0021_X INNER JOIN kritter0021_X AS kritter0021_X_1 ON
kritter0021_X.Product = kritter0021_X_1.Product
WHERE (((kritter0021_X_1.RANK)=[kritter0021_X].[RANK]+1))
ORDER BY kritter0021_X.Product, kritter0021_X.ProdDate,
IIf([kritter0021_X_1].[ProdDate] Is
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]));

 

[kritter0021]

Ok, Thanks! That worked! Now, if I wanted to add a unique id for each
product, how would I do that and involve that in this series of queries. I
have tried myself, but failed. Thanks!

Use these three queries --

kritter0021
SELECT YourTable.Product, YourTable.ProdDate, (SELECT COUNT(*) FROM
YourTable AS [XX] WHERE [XX].Product = YourTable.Product AND [XX].ProdDate
<= YourTable.ProdDate) AS RANK
FROM YourTable
ORDER BY YourTable.Product, YourTable.ProdDate;

kritter0021_X
SELECT kritter0021.Product, kritter0021.ProdDate, kritter0021.RANK
FROM kritter0021
UNION ALL SELECT YourTable.Product, NULL AS ProdDate, Max([RANK]) +1 AS RANK1
FROM YourTable INNER JOIN kritter0021 ON YourTable.Product =
kritter0021.Product
GROUP BY YourTable.Product;

SELECT kritter0021_X.Product, kritter0021_X.ProdDate,
IIf([kritter0021_X_1].[ProdDate] Is
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]))
AS Expr1
FROM kritter0021_X INNER JOIN kritter0021_X AS kritter0021_X_1 ON
kritter0021_X.Product = kritter0021_X_1.Product
WHERE (((kritter0021_X_1.RANK)=[kritter0021_X].[RANK]+1))
ORDER BY kritter0021_X.Product, kritter0021_X.ProdDate,
IIf([kritter0021_X_1].[ProdDate] Is
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]));

--
Build a little, test a little.

That works when you are calculating it between two fields, not when you are
trying to use the previous date in the same field. So

Product Date Dif Date

I need DiffDate beween the first two records calculated. Not sure how to
make it do that. 3/4/09 - 1/23/09 = 40.