Negative values are seen as positive (2 Viewers)

[JIC]

Switch you query to SQL view.

Copy and paste the SQL here.

We can show you the corrected SQL.

Then you can paste it over the SQL of your query.

Then switch back to query view and you will see how it is constructed by Access in the query designer.

SELECT [Date calc].ID, [Date calc].Amount, [Date calc].Duedate, [Date calc].DatePaid, DateDiff("d",[Duedate],[DatePaid]) AS Days, IIf((DateDiff("d",[DueDate],[DatePaid])>4),[Amount]*3/1000) AS Expr1, IIf([Expr1]="Yes",[Amount]*3/1000) AS DtCr, IIf([Days]<"0","0","No") AS Expr2
FROM [Date calc];

 

[JIC]

SELECT [Date calc].ID, [Date calc].Amount, [Date calc].Duedate, [Date calc].DatePaid, DateDiff("d",[Duedate],[DatePaid]) AS Days, IIf((DateDiff("d",[DueDate],[DatePaid])>4),[Amount]*3/1000) AS Expr1, IIf([Expr1]="Yes",[Amount]*3/1000) AS DtCr, IIf([Days]<"0","0","No") AS Expr2
FROM [Date calc];

I followed your instruction as in 17 and it seems to be working

 

[cheekybuddha]

One thing to double-check:

With table [Date calc] in design view, what datatypes are DueDate and DatePaid ?

Or if [Date calc] is a query, what is the SQL of the query?

 

[LarryE]

I think this is what you're asking.
Here is a query that compares when a payment is made against the due date using the DateDiff function. If the DateDiff function returns a negative number (which means the payment was before the due date) then no penalty is required. However, if the DateDiff function returns a positive number, a penalty should be applied.

Query:

SELECT Table1.exID, Table1.DueDate, Table1.PaymentDate, Table1.PaymentAmount, Table1.Balance, IIf(DateDiff("d",[DueDate],[PaymentDate])>0,[Balance]*0.05,0) AS PenaltyAmt, DateDiff("d",[DueDate],[PaymentDate]) AS DaysFromDueDate
FROM Table1;

Screenshot of Table1 contents and the query result:
View attachment 114010

SELECT Table1.exID, Table1.DueDate, Table1.PaymentDate, Table1.PaymentAmount, Table1.Balance, IIf(DateDiff("d",[DueDate],[PaymentDate])>0,[Balance]*0.05,0) AS PenaltyAmt, DateDiff("d",[DueDate],[PaymentDate]) AS DaysFromDueDate
FROM Table1;

DaysFromDueDate should be:

IIf([PaymentDate]>[DueDate],DateDiff("d",[DueDate],[PaymentDate]),Abs(DateDiff("d",[DueDate],[PaymentDate])))

because if the payment is early, then you want a positive number, so use the Abs() function to force a positive number. If the payment is late, then the number can be negative.