Nested IIf( with DMax and DateAdd not working correctly

[nightflight]

www.sendspace.com/file/ww1qrp

 

[vbaInet]

Right, what are the steps to reproduce the error? What form is it?

 

[nightflight]

frm_person is the form in question,
selecting a person via the combo box should display their previous certificate info in the subform top right,
changing the value in the text box "course date" should then auto populate DMax Cert, DMax Date -3mnths (test boxes I added to check that the Dmax and DateAdd formulae were working properly) and "new expiry date"

 

[nightflight]

Been doing a little more playing around with it, and it's bizarre.

if txt_coursedate > [certexpires]
it should add 36 months onto txt_coursedate
* which it does, provided the day value of txt_coursedate is > day value in [certexpires].
eg.
[certexpires] = 07/03/2009
txt_coursedate = 08/10/2010
txt_expdate = 08/10/2013

But if day value of txt_coursedate is < day value of [certexpires] then it adds 39months onto txt_coursedate
eg.
[certexpires] = 07/03/2009
txt_coursedate = 06/10/2010
txt_expdate = 06/01/2014



Similarly, if txt_coursedate<[certexpires]
and if day value in txt_coursedate is < day value in [certexpires]
it adds 39 months onto txt_coursedate, rather than 36 months onto [certexpires]
eg.
[certexpires] = 07/03/2009
txt_coursedate = 06/02/2009
txt_expdate = 06/05/2012 (it should be 07/03/2012)

But if day value in txt_coursedate is > day value in [certexpires]
it adds 36 months onto txt_coursedate
eg.
[certexpires] = 07/03/2009
txt_coursedate = 08/02/2009
txt_expdate = 08/02/2012 (it should be 07/03/2012)




If txt_coursedate is < [certexpires] - 3months,
It should add 39 months onto txt_coursedate

Which when day value txt_coursedate is < day value [certexpires] it does
eg.
[certexpires] = 07/03/2009
txt_coursedate = 06/12/2008
txt_expdate = 06/03/2012

but when day value txt_coursedate is > day value [certexpires],
it adds 36 months onto txt_coursedate
eg.
[certexpires] = 07/03/2009
txt_coursedate = 08/12/2008
txt_expdate = 08/12/2011

 

[vbaInet]

Try the attached.

 

[CraigDolphin]

The following should do the trick also, without using vba:

=IIf(
[txt_coursedate]>DMax("certexpires","qry_personcert"),
DateAdd("m",36,[txt_coursedate]),
IIf([txt_coursedate]>=[COLOR="DarkRed"]DateAdd("m",-3,DMax("certexpires","qry_personcert"))[/COLOR],
DateAdd("m",36,DMax("certexpires","qry_personcert")),
DateAdd("m",39,[txt_coursedate]))
)

The part in dark red is basically what you have in your txtCertMax control. This snippet doesn't require you to have that additional control. Note: I have broken up the code to improve readability. You would need to remove the carriage-returns at the end of each line for it to work.

Or you could use the switch function instead of the nested iif to achieve the same result:

=Switch(
[txt_coursedate]>DMax("certexpires","qry_personcert"),DateAdd("m",36,[txt_coursedate]),
[txt_coursedate]>=[COLOR="DarkRed"]DateAdd("m",-3,DMax("certexpires","qry_personcert"))[/COLOR],DateAdd("m",36,DMax("certexpires","qry_personcert")),
[txt_coursedate]<[COLOR="DarkRed"]DateAdd("m",-3,DMax("certexpires","qry_personcert"))[/COLOR],DateAdd("m",39,[txt_coursedate])
)

In all cases the logic is the same, just demonstrating that there are many ways to skin the same cat. Of course, the advantage to the vba function is readability by humans, which is hard to ignore!

 

[nightflight]

Neither of those work Craig, =o/

vbaInet; could you post up the vba code you used to achieve that results, i checked it last night at home and it worked perfectly, but can't open the uploaded DB through work's firewall.

 

[vbaInet]

In the form module:

Public Function GetExpDate(dDate As Variant)
    If IsDate(Me.txt_coursedate) Then
        Select Case CLng(CDate(Me.txt_coursedate))
            Case Is > CLng(CDate(Me.txtCertMax))
                GetExpDate = DateAdd("yyyy", 3, Me.txt_coursedate)
                
            Case CLng(CDate(Me.txtCertMax3)) To CLng(CDate(Me.txtCertMax))
                GetExpDate = DateAdd("yyyy", 3, Me.txtCertMax)
                
            Case Is < CLng(CDate(Me.txtCertMax3))
                GetExpDate = DateAdd("m", 39, Me.txt_coursedate)
                
            Case Else
                GetExpDate = Me.txt_coursedate
        End Select
    End If
End Function

The DateAdd() textbox was renamed txtCertMax. The DateAdd(-3) textbox was renamed txtCertMax3.

I've changed the Control Source of txtCertMax to:

=DateAdd("m",-3,[txtCertMax])

In the New Expiry Date textbox:

=GetExpDate([txt_coursedate])

 

[nightflight]

Works like a dream, thanks again vbaInet

 

[vbaInet]

You're welcome

 

[CraigDolphin]

In what way do they not work? They produce the same output as the vba function based on the examples you posted. And the attached db shows them functioning on the form.

Regardless, glad you have a working solution.