Query not filtering correctly/using parameters on calculated fields

[bo8482]

Hi
I have a query with a field that calculates whether a debtor balance has gone over a predefined percentage. This predefined percentage is stored in another table. The expression I use to calculate this field is:
OverDFL:IIf((([TopDebtorBalance]/[GrossAssignLedger])*100)>tblClientDetails!DFL,"Over DFL","")

This expression works fine, and it comes up with either "Over DFL" or "Under DFL" as appropriate.

I now want to base a report on the query, but only for "Over DFL" results. However when I type in "Over DFL" in the criteria field, it returns all results regardless of whether it is over/under.
I should point out that the query is form driven, where the form drives the dates between which the report should be shown.

Can anyone help? Many thanks

Below is the SQL code:

SELECT tblClientDetails.ClientNumber, tblClientDetails.ClientName, tblDebtors.DateOfLedger, tblDebtors.TopDebtor, tblDebtors.TopDebtorBalance, IIf((([TopDebtorBalance]/[GrossAssignLedger])*100)>tblClientDetails!DFL,"Over DFL","") AS OverDFL, [TopDebtorBalance]/[GrossAssignLedger] AS Ledger, tblDebtors.GrossAssignLedger, tblDebtors.InsuredUpTo, tblClientDetails.[PolicyEUK?], tblDebtors.Problem, tblDebtors.AddNotes, tblDebtors.RMComments, tblDebtors.[WebsiteVerify?], tblDebtors.EquifaxLimit, tblDebtors.EukTOPriorMonth, tblDebtors.DateofEquifax
FROM tblClientDetails LEFT JOIN tblDebtors ON tblClientDetails.ClientNumber = tblDebtors.ClientNumber
WHERE (((tblDebtors.DateOfLedger) Between [Forms]![DFLReport]![StartDate] And [Forms]![DFLReport]![EndDate]) AND ((tblClientDetails.[PolicyEUK?])=[Forms]![DFLReport]![cboPolicy]) AND ((IIf((([TopDebtorBalance]/[GrossAssignLedger])*100)>[tblClientDetails]![DFL],"Over DFL",""))="Over DFL")) OR (((tblDebtors.DateOfLedger) Between [Forms]![DFLReport]![StartDate] And [Forms]![DFLReport]![EndDate]) AND (([Forms]![DFLReport]![cboPolicy]) Is Null))
ORDER BY [TopDebtorBalance]/[GrossAssignLedger] DESC;

 

[pbaldy]

If my eyeballing of all the parentheses is accurate, you have

WHERE (blah and Over DFL) OR (blah)

so as long as the second set of criteria is met, the records will be returned. You may want to revisit your AND's & OR's and their parentheses.

 

[bo8482]

Thats an eagle eye you have there....

I deleted all the parameters and constructed it from scratch and that seemed to do the trick. So I guess there must have been a stray bracket in there somewhere! That was driving me crazy for most of today...

 

[pbaldy]

Glad you got it sorted out. I don't like that the query designer adds all those extraneous parentheses. It can make it very confusing to find this kind of thing.

 

[Brianwarnock]

I find that I have to paste it into Word and then edit with indents and check the parenthesis pairings etc.

Brian

 

[pbaldy]

I've done the same thing Brian. I paste the WHERE clause into Word and start deleting pairs of parentheses until I get down to the actually necessary ones (inside pairs out, if anyone tries it). You raise a good point about indenting too. I wish SQL view of an Access query would hold formatting like SQL Server does.

 

[boblarson]

I wish SQL view of an Access query would hold formatting like SQL Server does.

I agree and a few of us made that suggestion, very strongly, to the Access team. Here's hoping they will listen and put that in.

 

[pbaldy]

Same suggestion was made last year. It's either a low priority or it's not practical to do. Or both.

 

[boblarson]

Same suggestion was made last year. It's either a low priority or it's not practical to do. Or both.

Well, we can keep asking and perhaps it will occur at some point, eh?

 

[pbaldy]

Well, we can keep asking and perhaps it will occur at some point, eh?

[Bill Murray "Stripes" voice]

"That's a fact, Jack!"

[/Bill Murray "Stripes" voice]