How to count back # of days to current dat

[djclntn]

I would like to have a field which how many days passed from the date imputted to the current date.


Music/Artists Form


DatePlayed: 11/3/2015
*CurrentDate: 11/5/2015
Days Last Played (DLP): 2


Expression I tried: DLP: Format [(TDate]-Date())


*not a field

[bigot]

So DatePlayed is a field (what's the type?)

I'm not sure if it's a transposition error, but your opening brackets are in the wrong order

Format [(...

instead of

Format ([...

[djclntn]

Thanks for responding to my post. It's a Date/Time Field & yes I'm getting an "opening bracket are in the wrong order" error

[InsuranceGuy]

Replace

DLP: Format [(TDate]-Date())

with

DLP: [TDate]-Date()

[bigot]

Ok perfect, since the Date() function also returns Date/Time, you can just subtract the two.

Now you're using the "Format" function but omitting the second parameter, (which programmatically works, since that parameter is optional) but I think it defaults to "dd/mm/yyyyy" or "mm/dd/yyyy" (sorry, I'm not sure which, regardless...) you want to retrieve the number of days, not a date.

If you're thinking of using Format( ..., "d" ), think again! If the difference is more than 31 days, it will screw up! (I can explain on request, but it should be easy to figure out)

I suggest you do

CLng( Date() - TDate )

See "CLng" converts the difference to a Long. Which will give you the number of days.

Extra info: Now be careful not to use CInt, because Integers can only handle up to a value of 32,767. The current date (as of writing this) is 42,313. So, the difference between Date() and a TDate of 0 would overflow an Integer.

PS. InsuranceGuy's code above is incorrect (though I think he was just fixing your brackets without reading). You want to subtract TDate from Date ( Date() - TDate ), not the other way around ( TDate - Date() ). Think about it: If they listened to the song yesterday, subtracting today's date would give you -1, not +1!

[InsuranceGuy]

PS. InsuranceGuy's code above is incorrect (though I think he was just fixing your brackets without reading). You want to subtract TDate from Date ( Date() - TDate ), not the other way around ( TDate - Date() ). Think about it: If they listened to the song yesterday, subtracting today's date would give you -1, not +1!

Interesting take! I was just letting him keep his negative. I use negatives vs. positive day counts intentionally. )

[bigot]

Interesting take! I was just letting him keep his negative. I use negatives vs. positive day counts intentionally. )

That can be useful for certain types of date comparisons. Since DatePlayed will always be in the past and Date() generates today's date, your formula would never generate a positive