Calculating Dates, PlEASE HELP! THANKS!

  • 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)
 
Is this an Access question?

If so, then post you table and field names with datatype, sample data, and
what you want the results to look like.
 
kritter0021,

Oops, I think you are treating an Access table like and Excel spreadsheet.
No can do! For totaling in Access we have queries. Each *row* is a record,
each *field* (not cell) is a *part* of that record, not a field all by
itself.

If your data is set up like an Excel spreadsheet in Access move it back to
Excel and do your calculation. If in fact you want to use Access then your
tables will most likely have to be redesigned. However, we are going to
need to know more about what you are doing.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

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)
 
Yes.

Here is the data:
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
 
Yes.

Here is the data:
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.
 
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]));
 
Back
Top