Filtering Forms Based on the condition of two check boxes

[Benginner2212]

I am trying to filter forms based on the value of two check boxes. I have a check box called activeEquipment on a form that I am using to track if a piece of equipment is being actively used. The value of the box is True if the Piece of Equipment is being used and false when the equipment has been moved to storage. I have a second check box on the form named disposedEquipment that I am using to track if a piece of equipment has been removed from storage and thrown away.

I would like to open a set of forms where equipment that has been moved to storage or thrown away are filtered out. So far I have been able to figure out how to filter out either the equipment that has been moved to storage or the equipment that has been throw away but when I try to filter our both at the same time I get a data type mismatch error.

Here is my code:

Private Sub btnEquipmentTracking_Click()
Dim activeEquipment As String
Dim disposedEquipment As String
activeEquipment = "True"
disposedEquipment = "False"
Dim trackingCriteria

trackingCriteria = "activeEquipment=" & activeEquipment & " AND disposedEquipment='" & disposedEquipment & "'"
DoCmd.OpenForm "frmEquipTrack", acNormal, , trackingCriteria, , , "NewEquipment"

Debug.Print "tracking Criteria "; trackingCriteria


End Sub

when I look at the Debug.Print I can see that the criteria that I am checking for is correct, but I can't figure out the data mismatch error


Any help would be greatly appreciated

 

[Josef P.]

Why is activeEquipment in the data source a Boolean data field and disposedEquipment a Text data field?

 

[Gasman]

Booleans are not surrounded by "
They are either True or False
Why not just write them as that hardcoded?

 

[LarryE]

I am trying to filter forms based on the value of two check boxes. I have a check box called activeEquipment on a form that I am using to track if a piece of equipment is being actively used. The value of the box is True if the Piece of Equipment is being used and false when the equipment has been moved to storage. I have a second check box on the form named disposedEquipment that I am using to track if a piece of equipment has been removed from storage and thrown away.

I would like to open a set of forms where equipment that has been moved to storage or thrown away are filtered out. So far I have been able to figure out how to filter out either the equipment that has been moved to storage or the equipment that has been throw away but when I try to filter our both at the same time I get a data type mismatch error.

Here is my code:

Private Sub btnEquipmentTracking_Click()
Dim activeEquipment As String
Dim disposedEquipment As String
activeEquipment = "True"
disposedEquipment = "False"
Dim trackingCriteria

trackingCriteria = "activeEquipment=" & activeEquipment & " AND disposedEquipment='" & disposedEquipment & "'"
DoCmd.OpenForm "frmEquipTrack", acNormal, , trackingCriteria, , , "NewEquipment"

Debug.Print "tracking Criteria "; trackingCriteria


End Sub

when I look at the Debug.Print I can see that the criteria that I am checking for is correct, but I can't figure out the data mismatch error


Any help would be greatly appreciated

ACCESS doesn't know what "True" or "False" is. It only knows -1 (True) or 0 (False). Try:

Dim trackingCriteria as String
trackingCriteria = "[activeEquipment]=-1 AND [disposedEquipment]=0"

DoCmd.OpenForm "frmEquipTrack", acNormal, , trackingCriteria, , , "NewEquipment"

And make sure the activeequipment field and disposedeqipment field are both Yes/No Data types as defined in your table

 

[Josef P.]

ACCESS doesn't know what "True" or "False" is. It only knows -1 (True) or 0 (False).

Access-SQL (Jet/ACE-SQL) understands True and False.
Note: You should even use them if you use ODBC-linked tables, because e.g. the SQL server would not return records if the filter was set to -1 instead of True (TSql bit: True = 1), 0 for false will work.

 

[Benginner2212]

Booleans are not surrounded by "
They are either True or False
Why not just write them as that hardcoded?

A Combination of

Booleans are not surrounded by "
They are either True or False
Why not just write them as that hardcoded?

I would hard code them, but I always have lot of problems getting my AND statements to work inside the DoCmd.OpenForm command. I can never seem to get the data types surrounded by the correct symbols.

 

[Benginner2212]

Why is activeEquipment in the data source a Boolean data field and disposedEquipment a Text data field?

Both fields are Boolean Data types. I have a lot of trouble writing AND statements in VBA.

 

[Benginner2212]

Thank you all for the help. I was able to figure out the problem. I was just overcomplicating it by looking at tutorials for creating a where condition using two different data types.

Since both data type are boolean I was able to simplfiy my code to:

Private Sub btnEquipmentTracking_Click()
DoCmd.OpenForm "frmEquipTrack", acNormal, , "activeEquipment= True And disposedEquipment = False", , , "NewEquipment"

End Sub

and it worked like a charm.

 

[plog]

Run a query and see how many pieces of equipment are both in use and disposed of (true and true). I know users and I know data. My guess is you probably have at least 1 record like that.

d. The value of the box is True if the Piece of Equipment is being used and false when the equipment has been moved to storage. I have a second check box on the form named disposedEquipment that I am using to track if a piece of equipment has been removed from storage and thrown away.

Since these 3 options (In Use, Storage, Trash) are mutually exclusive, you should not have 2 fields, but one--EquipmentStatus.

Right now its possible for a piece of equipment to be In Use and also disposed of (true, true). You should eliminate that by just having one field that tells you the status (In Use, Storage, Trash). That makes this specific issue easier to achieve as well as further queries and reports where you want a list of itesm in a particular status.

 

[Benginner2212]

Run a query and see how many pieces of equipment are both in use and disposed of (true and true). I know users and I know data. My guess is you probably have at least 1 record like that.



Since these 3 options (In Use, Storage, Trash) are mutually exclusive, you should not have 2 fields, but one--EquipmentStatus.

Right now its possible for a piece of equipment to be In Use and also disposed of (true, true). You should eliminate that by just having one field that tells you the status (In Use, Storage, Trash). That makes this specific issue easier to achieve as well as further queries and reports where you want a list of itesm in a particular status.

This is a functionality issue that I am adding to a database that I have been revamping and fixing some issues, so I have to manually go through the data and figure out what equipment is still in use at our site, what equipment has been put into storage and what equipment has been disposed of.

Since I am using the value of the check boxes as a filter, I am not making the check boxes visible on the form. I have a combo box called cboOpStatus that has 3 possible selections. In Use, In Storage and Disposed. The value of the check box is set by the selection of the combo box.

The default value of the activeEquipment check box is true and then if a user selects the In Storage option on the cboOpStatus combo box the value of the activeEquipment check box is set to false.

The default value of the disposedEquipment check box is set to false and then when a user selects the Disposed option on the cboOpStatus combo box the value of the disposedEquipment is set to True.

On the form's before update event, I have a catch that prevents the cboOpStatus combo box from being changed once the value of the disposedEquipment check box is set to true.

 

[mike60smart]

Can you upload a copy of your database?

 

[Benginner2212]

Can you upload a copy of your database?

I have attached a copy of my database. In the time between my pervious post and this post, I realized that I was overcomplicating the issue. And instead of filtering the forms based on the value of a check box, I changed to filter off the value of a combo box.

Also considering redacted some of the data to avoid posting any information that might be used to identify equipment and locations. Just trying to cover my butt in case someone above my pay grade gets upset.

 

[Mike Krailo]

In the time between my pervious post and this post, I realized that I was overcomplicating the issue. And instead of filtering the forms based on the value of a check box, I changed to filter off the value of a combo box.

Wise decision.

You have a MfrID=66 that has no data in it (BLANK). You can prevent this from happening by requiring that field to contain data at the table level.

There is some redundant entries in the tblOpStatus:

 

[Benginner2212]

Wise decision.

You have a MfrID=66 that has no data in it (BLANK). You can prevent this from happening by requiring that field to contain data at the table level.
View attachment 108799
There is some redundant entries in the tblOpStatus:
View attachment 108798

I added a command button on the equipment tracking form that adds Manufacturers to the tblMfr table and while I was testing the code I was inadvertently adding blank data to the table. I thought that I had deleted all of the blank spots on the table when I was done testing the command button. I guess that I missed one.

The duplicate entries on the tblOpStatus are the result of me having to import data from an old database. I still need to go through the imported records from the previous database and change some of the information before I can delete the redundant entries and other entries that we are no longer using.