Negative values are seen as positive (1 Viewer)

[JIC]

I am using the DateDiff function in my query. When the difference is positive it is fine but when it is negative I want access to see it as negative
For example if my DateDiff value is negative it implies that the clients paid on time and there is no penalty, but if the value is positive it implies that they paid late and therefor a penalty is imposed on them.
What I actually get when I use the iif function is that access appears not to see it as negative but also as a positive
DtCr: IIf([Expr1]="Yes",[Amount]*3/1000)

 

[ebs17]

Expr1="Yes"

What does this have to do with positive or negative numbers?

 

[jdelano]

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:

 

[Gasman]

I am using the DateDiff function in my query. When the difference is positive it is fine but when it is negative I want access to see it as negative
For example if my DateDiff value is negative it implies that the clients paid on time and there is no penalty, but if the value is positive it implies that they paid late and therefor a penalty is imposed on them.
What I actually get when I use the iif function is that access appears not to see it as negative but also as a positive
DtCr: IIf([Expr1]="Yes",[Amount]*3/1000)

How exactly are you using DateDiff?

? datediff("m",date(),"01/02/2024")
-3 

? datediff("m","01/02/2024",date())
 3

 

[theDBguy]

@JIC Welcome to AWF!

 

[cheekybuddha]

DtCr: IIf([Expr1]="Yes",[Amount]*3/1000)

We need to know how [Expr1] is calculated (ie what is the expression).

Since you are coercing to a boolean, if [Expr1] returns any value other than 0, then it will coerce to True ("Yes"), even negative values.

In Access/VBA the value of True is already negative (-1).

 

[JIC]

How exactly are you using DateDiff?

? datediff("m",date(),"01/02/2024")
-3

? datediff("m","01/02/2024",date())
3

I put the due date and then date paid - in that sequence
My problem is that access seems to ignore the minus sign in front of the negative value and see it as an absolute value
In my case I basically want to let the negative values be ignored and only the positive values calculated. So if my client paid after the due date ie value is positive - then my iif must pick it up and calculate
But if the value is negative then it means my client paid in advance and no penalty is calculated

 

[cheekybuddha]

I put the due date and then date paid - in that sequence
My problem is that access seems to ignore the minus sign in front of the negative value and see it as an absolute value
In my case I basically want to let the negative values be ignored and only the positive values calculated. So if my client paid after the due date ie value is positive - then my iif must pick it up and calculate
But if the value is negative then it means my client paid in advance and no penalty is calculated

See my post above - please post [Expr1]

 

[JIC]

We need to know how [Expr1] is calculated (ie what is the expression).

Since you are coercing to a boolean, if [Expr1] returns any value other than 0, then it will coerce to True ("Yes"), even negative values.

In Access/VBA the value of True is already negative (-1).

Expr1: IIf([Days]>"4","Yes","0") - This was just my test because in real state the value should either be minus days or plus days

 

[JIC]

Expr1: IIf([Days]>"4","Yes","0") - This was just my test because in real state the value should either be minus days or plus days

Days: DateDiff("d",[Duedate],[DatePaid])
Expr1: IIf([Days]>"4","Yes","0")
DtCr: IIf([Expr1]="Yes",[Amount]*3/1000)

 

[cheekybuddha]

With [Expr1], you are comparing with a string instead of a number.

Try:

Expr1: IIf([Days]>4,"Yes","0")

 

[JIC]

In my last comment if the value is less than 0 ie -1 then access observe it as 1 and not as minus 1

 

[JIC]

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

This seems to look like what I intent to get

 

[JIC]

See my post above - please post [Expr1]

Expr1: IIf([Days]>"4","Yes","0")

 

[JIC]

This seems to look like what I intent to get

My problem is however, I am not familiar programming in Sql and are using a query design and built function

 

[JIC]

Copy Of Date calc Query Copy Of Date calc Query

ID	Amount	Duedate	DatePaid	Days	Expr1	DtCr	Expr2
1​	R5 000,00​	01-May-24​	07-May-24​	6​	Yes	15​	No
2​	R3 000,00​	01-May-24​	23-Apr-24​	-8​	Yes	9​	No

The problem is that in record 2 access see the -8 as days late and not as days early and as such it is now still calculating the dues but it shouls ignore it

 

[cheekybuddha]

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.

 

[JIC]

How exactly are you using DateDiff?

? datediff("m",date(),"01/02/2024")
-3

? datediff("m","01/02/2024",date())
3

Yes thanks my problem is however that when my days are minus days then access still calculates penalties wheras I don't want it that way. Only of the days are positive

 

[The_Doc_Man]

You showed us:

Days: DateDiff("d",[Duedate],[DatePaid])
Expr1: IIf([Days]>"4","Yes","0")
DtCr: IIf([Expr1]="Yes",[Amount]*3/1000)

However, you could write this:

IIF( ( DateDiff( "d", [DueDate], [DatePaid] ) > 4 ), [Amount]*3/1000, 0 )

Of course, if you actually needed to keep the intermediates for other purposes, you would compute them separately (as you showed us) and that's OK too.

Note, however, that your DAYS computation will return an integer but you are comparing (in Expr1) as though it were a string. It isn't. It's a LONG. David (cheekybuddha) posted this but your subsequent posts seem to continue to make the same mistake. So I'll be more explicit.

DateDiff function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

The start of the description of the function says "Returns a Variant (Long) specifying the number of time intervals between two specified dates."

If you are comparing "4" to a LONG, VBA "coerces" the long to become a string and thus you get the equivalent of IIF( CSTR(longvalue) > "4" ), ... which surely isn't what you meant. You are comparing a number to a string and that means that even if VBA converts the long value to a string, you do the comparison in string order, not in numeric order. All you have to do is LOSE THE EXTRA QUOTES AROUND THE "4" and you should be good. Or at least better.

 

[cheekybuddha]

Yes thanks my problem is however that when my days are minus days then access still calculates penalties wheras I don't want it that way. Only of the days are positive

Follow my instructions in Post #17.

If you don't know how to do it, then let us know.