Finding DateDiff using DIM values

[Locopete99]

Hi Guys,

I need to find the difference in months between two values.

One value would be Now() the second a date from a record.

The only problem is that I would need to re-constitute the date from the record.

The date on the record is being entered as "Sept-17" for example.

Using the idea that some times its easier to work around bad habits instead of trying to change the way a lot of people work I have made the below code.

This works in re-constituting the date, but I dont seem to be able to use it with the datediff function.

Any help?

Dim m1 As String
Dim m2 As Variant
Dim d1 As Date
Dim ye1 As Variant
Dim ddif As Variant

m1 = DLookup("[Month 12]", "Tbl_Forecast", "[Subba2]=" & "'" & [Subba] & "'")
m2 = Month(m1)

If m2 < 10 Then
m2 = "0" & Month(m1)
Else
m2 = Month(m1)

End If

ye1 = "20" & Right(m1, 2)
d1 = "01/" & m2 & "/" & ye1

ddif = DateDiff(m, d1, Now())

 

[Locopete99]

to explain it a bit.

If you take the date as Sept-17 it works as follows:

m1 - gets the record from the table
m2 - gets the month, so in the example above 9
The if function makes anything under 10 a double digit month - 09

ye1 - takes the last two digits and makes it a year - 2017
d1 - adds it all together to make a date - the first of the month - 01/09/2017

 

[Locopete99]

Fixed it!

used

ddif = DateDiff("m", Date, d1)

 

[arnelgp]

to be able to use of what you have right now,
change this:

d1 = "01/" & m & "/" & ye1

because Access only accepts English(EN)
date format:

d1 = CDate(m & "/" & 1 & "/" & ye1)
ddif =DateDiff("m", d1, Date)

 

[isladogs]

Actually Arnel you mean US date format mm/dd/yyyy
English is dd/mm/yyyy