Solved Split form not filtering to 0 with drop-down filters

[Kayleigh]

I am having trouble with a split form. When filtering one field to 0 it will not display all the records. It will work when I use a number filter 'equals 0' though.
I have checked if there are any underlying filters on the form and also if it works to do in the underlying query but not managed to solve it.
Can anyone help me troubleshoot this issue?

 

[theDBguy]

To help you with that, you might have to post a sample copy of the db with test data.

 

[Kayleigh]

Difficult as it is sql server

 

[GPGeorge]

Spoiler: ETC.

I am having trouble with a split form. When filtering one field to 0 it will not display all the records. It will work when I use a number filter 'equals 0' though.
I have checked if there are any underlying filters on the form and also if it works to do in the underlying query but not managed to solve it.
Can anyone help me troubleshoot this issue?

Help us by providing enough context to understand the nature of the problem.

For example:

How are you attempting to apply a filter?
Why do you expect 0 to return all records?
What is the SQL of that underlying query?

Thanks.

 

[Pat Hartman]

Do you have any formatting applied to the table? Even fixing decimal positions to 2 can mask actual values since 0.00005 will round to 0.00 but will NOT compare to 0.00 since the actual value will be used, NOT the formatted value. People run into this all the time. NEVER format data at the table level. All that can do is to mask the actual value. Formatting never changes the actual value. So, you can believe your lyin' eyes or you can believe the query with criteria.

 

[Kayleigh]

This is the SQL

SELECT tblTenancy.TYNAME, tblTenancy.TYADD1, tblTenancy.TYADD2, tblTenancy.TYADD3, tblTenancy.TYADD4, tblComments.*, tblInvoice.ItemDate, tblInvoice.ItemDesc, [ItemAmount]+[ItemAmVAT] AS cfInvTotal, [OutStand]+[OutStVAT] AS cfOutStand, [received]+[recvat] AS cfReceived, tblTenancy.TYSTATUS, tblTenancy.TYPSTCD, tblTenancy.TYSTART, [tyadd1] & [tyadd2] & [tyadd3] & [tyadd4] & [typstcd] AS FullAddress, [fldnotes] & [fldnote2] AS [Note]
FROM (tblTenancy INNER JOIN tblComments ON tblTenancy.TYCODE = tblComments.TYCODE) INNER JOIN tblInvoice ON tblComments.fldLastInvoiceID = tblInvoice.ID
ORDER BY tblInvoice.ItemDate DESC;

So there is no shortcut way of doing this without reversing the data type on table level? (prefer to avoid as it would have ripple effect across DB)

 

[arnelgp]

you need to fix it at table level or use patches everytime you encouter problem with this field.

 

[Kayleigh]

So it seems to work when I convert all values between 0.01 and -0.01 to 0 so all the tiny values are filtered out.

 

[Minty]

In SQL Server, you see this frequently where people have used Float datatypes instead of a fixed number of places Decimal(18,4)
By fixing the number of places you dramatically reduce floating-point errors and the type of issues you are possibly seeing.