My query doesn't work

[AlvaroCity]

Hey folks!

I bumped into a problem on my database.

I've got a query which says either a invoice is completely paid off or not. The thing is that in my query I have a field called "Total" and another one called "AmountPaid" and I created another which turns out either the "AmountPaid" is greater than "Total" to check if the invoice has been paid off as shown:

PaidOff: SiInm([AmountPaid]<[Total];"No";"Yes")

The problem is that sometimes the "amountpaid" is not greater than "total"(so the invoice hasn't been paid off) and the query displays "Yes" :banghead:

Why is this??

Thank you in advance.

 

[June7]

What is [AmountPaid] when that happens? Is it Null? If so, then since Null is not True (it isn't anything), the expression must return the Not True result.

Cannot compare anything to Null.

One way to deal with Null is to wrap the field in expression that will provide a value if Null. Nz() is one way.

Nz([AmountPaid],0)

 

[JHB]

What function is SiInm?
Google it, but it doesn't give something I could read.

 

[isladogs]

Shouldn't that be IIf?

 

[Gasman]

Also if in Edinburgh (UK) shouldn't the semi colons be commas?

 

[AlvaroCity]

Sorry I had the Spanish version
I dont know why in Spanish is different.

IIf([AmountPaid]<[Total],"No","Yes")

Regarding to June7, the problem is that someone paid a part of the debt ([AmaountPaid]) and the query returns that has been paid off.

I must say that [Total] is a Dsum quotation, just in case.

 

[June7]

But what is the value of AmountPaid for record when it returns Yes and should be No?

 

[AlvaroCity]

In this case, the value [Amountpaid] was half of the total debt and it returned that the total was paid off...:banghead: when it wasn't

 

[June7]

Well, if both fields have data then the expression should work. I don't see anything wrong with the syntax. I would suggest posting db so we could analyze but if it is all in Spanish that could be difficult, however am willing to give a try. Follow instructions at bottom of my post.

 

[Dreamweaver]

I use a tick box as there are a lot of things that can go wrong with getting paid plus I don't like IIF() this is how I deal with payments


Some payment methods auto pay if invoiced = payed I.E. the payed tick box gets ticked

 

[Gasman]

In this case, the value [Amountpaid] was half of the total debt and it returned that the total was paid off...:banghead: when it wasn't

So what was the value to Total at that time?