Solved Split form not filtering to 0 with drop-down filters (1 Viewer)

Kayleigh

Member
Local time
Today, 17:33
Joined
Sep 24, 2020
Messages
616
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

I’m here to help
Staff member
Local time
Today, 09:33
Joined
Oct 29, 2018
Messages
19,135
To help you with that, you might have to post a sample copy of the db with test data.
 

Kayleigh

Member
Local time
Today, 17:33
Joined
Sep 24, 2020
Messages
616
Difficult as it is sql server
 

GPGeorge

Grover Park George
Local time
Today, 09:33
Joined
Nov 25, 2004
Messages
764
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

Super Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 19, 2002
Messages
36,837
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

Member
Local time
Today, 17:33
Joined
Sep 24, 2020
Messages
616
This is the SQL
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:33
Joined
May 7, 2009
Messages
16,779
you need to fix it at table level or use patches everytime you encouter problem with this field.
 

Kayleigh

Member
Local time
Today, 17:33
Joined
Sep 24, 2020
Messages
616
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

AWF VIP
Local time
Today, 17:33
Joined
Jul 26, 2013
Messages
9,364
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.
 

Users who are viewing this thread

Top Bottom