Build date calculation

  • Thread starter Thread starter Harry
  • Start date Start date
H

Harry

Field: | March Rpt | April Rpt |<exp>| May Rpt |<exp>|
Table: | March | April | | May | |
List: | 2/23/03 | 4/02/03 | | 5/22/03 | |

What <exp> can I insert btw Fields that calculates the
diff in days:

Example: 2/23/03 - 4/02/03 = difference
4/02/03 - 5/22/03 = difference
 
Harry

Not sure I understand your data structure, which would be fairly critical to
know before offering suggestions. It looks like you have a table per month,
which, if true, is a very "spreadsheetly" thing to do. Access is a
relational database, and to maximize your use of your data (and to make your
life and work easier), consider reviewing "normalization" in Access HELP.

Or, maybe I've mis-inferred from your description...

Good luck

Jeff Boyce
<Access MVP>
 
Assuming all fields are date/time values:

SELECT [March Rpt], [April Rpt], CInt([April Rpt] - [March Rpt]) As
DaysMarApr,
[May Rpt], CInt([May Rpt] - [Apr Rpt]) As DaysAprMay
FROM MyTable

This works because date/time values internally are a day value as the
integer portion (the number of days since December 30, 1899) and the time
value in the decimal portion as a fraction of a day accurate to seconds.
Subtract one date/time value from another, and you get the number of days in
between.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Add new field in your query "FebAprDiff:[April Rpt]-
[March Rpt]" This should return the number of days.
Change dates as needed.

Denny
 
Denny

The down side to this approach is having to add a field and change the query
every time you want to compare a different pair.

Jeff Boyce
<Access MVP>
 
Back
Top