Filtering Forms on a checkbox (1 Viewer)

vhanus2

Registered User.
Local time
Today, 02:54
Joined
Aug 13, 2018
Messages
10
Hello everyone,

Im currently working on a database, and im trying to filter on when a checkbox is marked as false; however the filter only works on true. When changing to false, it gives me a invalid call procedure.

This is my SQL if it helps:
SELECT tblBoxNumbers.ID, tblBoxNumbers.IronMountainID, [tblRecord Departmenttype].RecordType, tblDocumentTypes.DocType, tblBoxNumbers.[SenttoDestroy?], tblBoxNumbers.EmailsentDate, DateAdd("yyyy",[tblDocumentTypes]![Years],[tblBoxNumbers]![DocumentDate]) AS Expr1, tblBoxNumbers.DocumentDate
FROM [tblRecord Departmenttype] INNER JOIN (tblDocumentTypes INNER JOIN tblBoxNumbers ON tblDocumentTypes.DocumentTypeID = tblBoxNumbers.DocumentTypeID) ON [tblRecord Departmenttype].RecordTypeID = tblBoxNumbers.RecordTypeID
WHERE (((tblBoxNumbers.[SenttoDestroy?])=True) AND ((DateAdd("yyyy",[tblDocumentTypes]![Years],[tblBoxNumbers]![DocumentDate]))<Date()));

Any help would be appreciated!
 

Ranman256

Well-known member
Local time
Today, 05:54
Joined
Apr 9, 2015
Messages
4,339
have the form show all records, but filter on the checkbox value

Code:
sub chkBox_Afterupdate()
me.filter = "[Field]=" & me.chkBox.value
me.filterOn = true
end sub

'default at load
sub form_load()
me.filter = "[Field]=true"
me.filterOn = true
end sub
 

bastanu

AWF VIP
Local time
Today, 02:54
Joined
Apr 13, 2010
Messages
1,401
I take it you don't really want to "filter" but you want to limit your query results using a criteria on your boolean (Yes/No) field [SentToDestryo?], is that correct? Do you actually have data that would satisfy the conditions ([SentToDestroy?]=False and dateinfuture<Today)? Where is the query(SQL statement) used, in VBA, as a record source for a form, report???

Cheers,
Vlad
 

vhanus2

Registered User.
Local time
Today, 02:54
Joined
Aug 13, 2018
Messages
10
Hi Vlad,

I originally had this as a filter on load, but i recently opened my form and it seams as though it does not work. I have tried it as a filter on load, in the record set of the form, SQL, and as a toggle filter.

There is data that supports the field as a no/false; however it does not show when trying to have only those that are viewed.
 

bastanu

AWF VIP
Local time
Today, 02:54
Joined
Apr 13, 2010
Messages
1,401
What happens if you hard-code it in the record source of the form (remove all filters on both query and form and set it as a criteria in the query that is used as the record source of the form)?

Cheers,
Vlad
 

Users who are viewing this thread

Top Bottom