Date calculations (1 Viewer)

[hardhitter06]

Hi,

On an access 2010 form, I have created a formula to calculate the "Year" difference between two dates and I want to add this difference to one of my form fields that feeds my table.

I am probably approaching this wrong but this is what I have done.

Added a hidden text field "Text12" which stores today's date "=Date()".

I then added another hidden text field "Text10" which stores this formula:
"=Year([Text12])-Year([FirstMissingDate])"

The difference is stored in Text10 but I need this to feed back into my table so I need it stored in an actual field in the table called "YTDMissing".

So I guess my questions are:

1. Is this the best way to do this

2. If so, how do I push the Text10 data (date calculation difference) into the field "YTDMissing"?

Thank you,

 

[Isskint]

All you need do is use the DateDiff function and include this as a default value for your YTDMissing field.

if date() is before FirstMissingDate then
=DateDiff("yyyy",Date(), FirstMissingDate)

otherwise
=DateDiff("yyyy", FirstMissingDate,Date())

 

[Beetle]

1. Is this the best way to do this

No.

2. If so, how do I push the Text10 data (date calculation difference) into the field "YTDMissing"?

You shouldn't push it to the table at all. This is calculated data that should not be stored, just calculated as needed. You can do this with a calculated field in a query. Create a query that includes all the fields you need from your table (except the YTDMissing field, which shouldn't exist in the first place), then add a calculated field like the following;

YTDMissing: Year(Date()) - Year([FirstMissingDate])

Then use this query as the record source of your form in place of the table.

 

[Addyman]

To expand on what Beetle correctly said, it is considered bad design to store calculated values because if you ever change any of the values which were used in the calculation (e.g. one of the dates in your table) then you would have to update your calculated value as well.

By calculating as needed, you will always be using the most up to date data and will always get the correct result.

 

[hardhitter06]

Thank you guys...I'm going with Beetle/Addy advice because that makes sense.

 

[hardhitter06]

It seems like my formula is slightly off or I need an extra piece.

I need it to take the difference of total years...not just subtract Today's Year - the First Missing Year.

For example, today is 7/18/12...and the first missing date was 11/1/2009. This formula is returning 3 but since November 1st of 2012 hasn't arrived yet, I need the formula to reflect 2 years.

OR

Include the Year and month difference...

 

[Beetle]

If you just want the years, leaving out any remainder months, try this;

Fix(DateDiff("m",[FirstMissingDate],Date())/12)

If you want a string like "X years Y months" you can use something like;

Fix(DateDiff("m",[FirstMissingDate],Date())/12) & " years " & DateDiff("m",[FirstMissingDate],Date()) Mod 12 & " months"

The above will only take full months into account, so using your two example dates it would return;

2 years 8 months

Depending on whether you store just the Date or the Date and Time, this could be expanded to additional levels of detail (day, hour, minute, second) if you wanted.

 

[hardhitter06]

YTDMissing: Fix(DateDiff("m",[FirstMissingDate],Date())/12) & " years " & DateDiff("m",[FirstMissingDate],Date()) Mod 12 & " months"

I'm having issues with this.

If Today's Date is 8/14/12 and FirstMissingDate is 6/30/12, 2 months is being returned and I can't have this round up...

Is there a way to set this forumla up so it would return 1 month in this case?

Or is it easier to calculate just in months and remove years from the equation? Again with no rounding up...

Thanks

 

[Beetle]

Let's use a function so we can have a little better control over what exactly is returned.

Place this function in a standard module in your application;

Public Function fYearsMonths(dteStart As Date, dteEnd As Date) As String

    Dim i As Integer, intMonths As Integer, intYears As Integer
    Dim strMonths As String, strYears As String
    
    i = DateDiff("m", dteStart, dteEnd) + _
       (dteEnd < DateSerial(Year(dteEnd), Month(dteEnd), Day(dteStart)))
       
    intYears = Fix(i / 12)
    intMonths = i Mod 12
    
    strYears = IIf(intYears = 1, " year ", " years ")
    strMonths = IIf(intMonths = 1, " month", " months")
    
    fYearsMonths = IIf(intYears = 0, "", intYears & strYears) & intMonths & strMonths

End Function

Then call it in your query like;

YTDMissing: fYearsMonths([FirstMissingDate], Date())

 

[hardhitter06]

Wow, you are a very smart man. Thanks so much!!

 

[hardhitter06]

Beetle,

I'm back and I'm having one more issue. The calculations are perfect! and I thank you so much for that.

I'm trying to now order these in a query/report and since it's not reading them as dates - the order is incorrect.

For example when I order them in descending order I get results like these:

9 months
7 months
5 years 7 months
31 years 8 months
1 year 8 months

And so on...its arranging them as integers which isn't correct as you can see.

I believe I either need to make an adjustment in the SQL of the query to convert the result to a "Date" field mentality (have no idea how to do that lol)

or

Change this formula to only calculate in months...I do like Years and Month split but if I have to get rid of it to make this work i will...

How would I accomplish this ?

 

[Beetle]

If you like the values that the current function is returning then there is no reason you can't keep that and just add two additional fields for sorting. We can create two additional small functions that will return the years and months separately that can be used for sorting by these two additional fields in your query. You can choose whether or not you want to display these additional fields in your query. They can be used for sorting without being displayed.

So if we take the same logic from the first function and just reduce it a bit we can get the years and months separately for sorting. First the years;

Public Function fYears(dteStart As Date, dteEnd As Date) As Integer

    Dim i As Integer
    
    i = DateDiff("m", dteStart, dteEnd) + _
       (dteEnd < DateSerial(Year(dteEnd), Month(dteEnd), Day(dteStart)))
       
    fYears = Fix(i / 12)
   
End Function

then the months;

Public Function fMonths(dteStart As Date, dteEnd As Date) As Integer

    Dim i As Integer
    
    i = DateDiff("m", dteStart, dteEnd) + _
       (dteEnd < DateSerial(Year(dteEnd), Month(dteEnd), Day(dteStart)))
       
    fMonths = i Mod 12
    
End Function

Place those functions in your standard module then add two additional columns to your query;

fYears([FirstMissingDate], Date())

fMonths([FirstMissingDate], Date())

and sort those columns descending. Just make sure the fYears column is before the fMonths column in the sort order.

Those two functions are actually simple enough that you could just write the logic as an expression directly in the query, but I personally would still prefer to have them as functions because it's more efficient if you later decide you want to use them in more places in your application, or if you want to modify them to return different results. Better just to have one place to store the logic.

 

[hardhitter06]

Again thank you!