Calculate Diff Date?

  • Thread starter Thread starter kritter0021
  • Start date Start date
K

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.
 
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.
 
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]));
 
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!

KARL DEWEY said:
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.


kritter0021 said:
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.
Click to expand...
Click to expand...
 
Back
Top