Help with conditional field on form with dates (1 Viewer)

I am new to this forum so I hope I explain this ok.

I have a form that I want to allow users to check the result between two dates;

txt_Date2
txt_Date3

The result appears in the field Txt_Result2 in the format of number of years, Months and Days. I am trying to provide a visual check for the user to see If the result is less than 6 months and 21 days.

I have used a function credited to raskew see at the bottom of the post that provides me with this result and this works.

I want to produce a message of OK or Not ok if the result meets the criteria of < 6 months and 21 days (0.6.21) and this is where I am struggling.

In my mind simplistically it is;

=IIf([Txt_Result2]<0&" Years".6&" Months".21&" Days","OK","Not OK")

or =IIf([Txt_Result2]<0.6.21,"OK","Not OK"

It either reads OK all the time.

I would be most grateful if someone could point me in the right direction please.


Many thanks


Function fAge(dteStart As Variant, dteEnd As Variant) As Variant

'*******************************************
'Purpose: Accurately return the difference
' between two dates, expressed as
' years.months.days
'Coded by: raskew
'Inputs: From debug (immediate) window
' 1) ? fAge(#12/1/1950#, #8/31/2006#)
' 2) ? fAge(#12/30/2005#, #1/2/2006#)
'Outputs: 1) 55.8.30
' 2) 0.0.3
'*******************************************
Dim intHold As Integer
Dim dayhold As Integer

'correctly return number of whole months difference
'the (Day(dteEnd) < Day(dteStart)) is a Boolean statement
'that returns -1 if true, 0 if false
intHold = DateDiff("m", dteStart, dteEnd) + (Day(dteEnd) < Day(dteStart))

'correctly return number of days difference
If Day(dteEnd) < Day(dteStart) Then
dayhold = DateDiff("d", dteStart, DateSerial(Year(dteStart), Month(dteStart) + 1, 0)) + Day(dteEnd)
Else
dayhold = Day(dteEnd) - Day(dteStart)
End If

fAge = LTrim(Str(intHold \ 12)) & " Years " & LTrim(Str(intHold Mod 12)) & " Months " & LTrim(Str(dayhold)) & " Days"
End Function

The problem you still have is that each month has a different length between 28 & 31 days so you may be better deciding on a cut off number of days e.g 200

Next use the DateDiff function to calculate days
Dim N as Integer
N =DateDiff("d", dteStart, dteEnd)

If N<200 Then
MsgBox "OK"
Else
MsgBox "Not OK"
End If

Alternatively you could convert both your date fields to Long integers using CLng.
Then subtract the difference to give the number of days between them

Thank you for taking the time to respond. I will have a really good l think about it as I hadn't thought about doing it that way. The rule is six months (no matter how many days are in the month) and then a 21 day buffer. Thank you again for your time.

The rule is six months (no matter how many days are in the month) and then a 21 day buffer.

Click to expand...

I guessed that from your first post.
If you don't mind me saying so, whoever came up with that rule isn't a programmer.

Its possible to write code to fit that rule but choosing a set number of days is much easier. I chose 200 as that almost exactly fits the rule anyway.

They are most definitely not programmers . The rule causes numerous issues as you can imagine. Thanks again.