My query doesn't work (1 Viewer)

AlvaroCity · Dec 3, 2018

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:

Code:

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 · Dec 3, 2018

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 · Dec 3, 2018

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

isladogs · Dec 3, 2018

Shouldn't that be IIf?

Gasman · Dec 3, 2018

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

AlvaroCity · Dec 4, 2018

Sorry I had the Spanish version

I dont know why in Spanish is different.

Code:

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 · Dec 4, 2018

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

AlvaroCity · Dec 13, 2018

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 · Dec 13, 2018

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 · Dec 13, 2018

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 · Dec 13, 2018

AlvaroCity said:
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?

My query doesn't work (1 Viewer)

AlvaroCity

Registered User.

June7

AWF VIP

JHB

Have been here a while

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

AlvaroCity

Registered User.

June7

AWF VIP

AlvaroCity

Registered User.

June7

AWF VIP

Dreamweaver

Well-known member

Attachments

Gasman

Enthusiastic Amateur

Similar threads

Users who are viewing this thread