Some help with Date calculation

[djclntn]

In my dealership dbs one of my fields is, ‘DatePurchased.’ From the ‘DatePurchased’ field I created a field ‘DOL’ (Days On Lot) which counts the days between the ‘DatePurchased’ to the current date: DOL: (Date()-[DatePurchased]). E.g. DatePurchased 8/27/2012, DOL 129. Now, how can I show (calculate) how many MONTHS have passed since ‘DatePurchased’ (or DOL) & the current date?
Thanks in Advance

[pbaldy]

Try the DateDiff() function.

[orange]

Hi Dave.
You can't directly do the days to month conversion because different months have different number of days.
If you want to get a reasonable approximation, you could divide your days calculation by 30, or 30.23( or you pick the value) and it'll be pretty close.

The other option is
MonthsOnLot = Datediff("M",DatePurchased,Date())

I forget which Date has to be first. If this gives a negative number then reverse the Date order.


Jeeez: I spent a few seconds reading this over and Paul has answered.. oh well.

[djclntn]

Much thanks pd & orange!

[pbaldy]

Happy to help!

[djclntn]

Hey pd &/or orange, I have another ? I have 2-flds in my dealership dbs, 1) 'StckNum' & 'VINNum' Now the 'StckNum" are the last 6-characters of the 'VINNum.' How/or is there a way that when I enter in the 'VINNum' the last 6-characters would automatically be entered in the 'StckNum'? If so, how would I go about?

[pbaldy]

I wouldn't save it, but:

http://allenbrowne.com/casu-14.html

Either way using the Right() function.

[orange]

I wouldn't save/store it as a separate field either. If you have to report StckNum, as Paul says, Right(VinNum,6)

[djclntn]

I'm confused, what's the problem with saving it?

[June7]

Reasons:

1. duplication of data

2. data based on calculation of other data

3. requires code to save calculated value

4. saved value can become 'out of sync' with source data

[djclntn]

Hey June :-) I might be a fool, but I'll take the risk...

[June7]

As Allen Browne notes in the referenced article, there are times when saving calculated value is justified. However, not seeing that in this case but if you must, something like:

Me.StckNum = Right(VinNum,6)

The real trick is figuring out what event to put the code in. Perhaps the AfterUpdate event of VinNum textbox.

[djclntn]

Actually, I went to the Record Source & in the query changed the StckNum to [StckNm: Right([VinNumber],6)] It might not be right, but it's working for me. If it bites me in the a** I'll own up to it

[June7]

That does not save data to table. That does what we were all advising, simply calculate the value in query.

[djclntn]

As it bites me right in the A**, thanks