Crazy Problem - IIF and Dates (1 Viewer)

fenhow

Registered User.
Local time
Today, 08:48
Joined
Jul 21, 2004
Messages
599
Hi, I am using this statement.

Format(IIf([Last Payment Made]<DateAdd("d",3,[DueDate])),0,[AmountDue]*0.1))),"Currency")

Last Payment Made is a date field Dlookup, short date format.
Due Date is in the table, short date format.

The issue:

Say my DueDate is 1/1/2016

If my Last Payment Made Date is on or before 1/4/2016 it shows 0 as it should

If my Last Payment Made Date is after 1/4/2016 up to 1/9/2016 it triggers [AmountDue]*0.1 as it should

Now here it is -- If my Payment Made Date is anytime after 1/9/2016 it will not trigger [AmountDue]*0.1... anything with 1/10/2016 or any 1/XX/2016. It appears that double digits in the "Day" kill the [AmountDue]*0.1 if it is > greater than the due date +3....

Any ideas why? I have spent countless hours on this and cannot find a reason...

You will forever be my hero if this can be solved...

Fen How
 

fenhow

Registered User.
Local time
Today, 08:48
Joined
Jul 21, 2004
Messages
599
Solved.. It seems by adding the Datevalue() function, to each of the date fields resolved the issue. In the event anyone wanted to know..
Thanks to Bob Larson who posted something similar on the forum.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:48
Joined
Jan 23, 2006
Messages
15,364
I mocked up your set up. Note I am in Canada so regional settings will show DD/MM/YY

I think there was an issue with your Format bracketing.

Code:
Sub testdtStuff()
' original   Format(IIf([Last Payment Made]<DateAdd("d",3,[DueDate])),0,[AmountDue]*0.1))),"Currency")
    Dim LastPaymentMade As Date: LastPaymentMade = #1/19/2016#    '#10/1/2016#
    Dim dueDate As Date: dueDate = #1/1/2016#
    Dim amountDue As Single: amountDue = 200.5
    Debug.Print "sample ---   LastPaymentMade   " & LastPaymentMade
    Debug.Print "3 days beyond dueDate is  " & DateAdd("d", 3, [dueDate])
    Debug.Print IIf([LastPaymentMade] < DateAdd("d", 3, [dueDate]), 0, [amountDue] * 0.1)
    Debug.Print Format(IIf([LastPaymentMade] < DateAdd("d", 3, [dueDate]), 0, [amountDue] * 0.1), "Currency")
End Sub

some sample outputs:

sample --- LastPaymentMade 01/01/2016
3 days beyond dueDate is 04/01/2016
0
$0.00

sample --- LastPaymentMade 02/01/2016
3 days beyond dueDate is 04/01/2016
0
$0.00

sample --- LastPaymentMade 05/01/2016
3 days beyond dueDate is 04/01/2016
20.05
$20.05

sample --- LastPaymentMade 19/01/2016
3 days beyond dueDate is 04/01/2016
20.05
$20.05

sample --- LastPaymentMade 20/06/2016
3 days beyond dueDate is 04/01/2016
20.05
$20.05
 

Users who are viewing this thread

Top Bottom