Check box on a form

[sesling]

Currently I am using 3 text boxes (status1, status2, status3) on a form and passing the values to a query. The operator will enter one of three statuses (Open, Closed, Pending) in one or more text boxes. The operator will execute a query and the results will be returned based on what they enter. Below I have listed the criteria statement from the query.

[forms}![Account Records]![status1] or [forms}![Account Records]![status2] or [forms}![Account Records]![status3]

I know I can default values in the box and have the operator either keep or remove them to change the results of the query.

However, we will be adding more statuses to our table. Instead of the operator entering the value I would like to use a check box so when the operator selects the box a value will be passed to the query. If the box is not selected the value will not be passed to the query. Is this possbile?

[ADezii]

Currently I am using 3 text boxes (status1, status2, status3) on a form and passing the values to a query. The operator will enter one of three statuses (Open, Closed, Pending) in one or more text boxes. The operator will execute a query and the results will be returned based on what they enter. Below I have listed the criteria statement from the query.

[forms}![Account Records]![status1] or [forms}![Account Records]![status2] or [forms}![Account Records]![status3]

I know I can default values in the box and have the operator either keep or remove them to change the results of the query.

However, we will be adding more statuses to our table. Instead of the operator entering the value I would like to use a check box so when the operator selects the box a value will be passed to the query. If the box is not selected the value will not be passed to the query. Is this possbile?

I am definately not an advocate of the IIF() Function, but it seems appropriate under this circumstance. Hopefully, this will point you in the right direction.

'If chkStatus is checked, IIF returns the value in the Status1 Field
'on the Account Records Form - if it is not checked, an empty String is
'returned.
 
IIf(Me![chkStatus], [Forms}![Account Records]![Status1], "")

[sesling]

I am definately not an advocate of the IIF() Function, but it seems appropriate under this circumstance. Hopefully, this will point you in the right direction.

'If chkStatus is checked, IIF returns the value in the Status1 Field
'on the Account Records Form - if it is not checked, an empty String is
'returned.
 
IIf(Me![chkStatus], [Forms}![Account Records]![Status1], "")

I think it will work. Where would the IIF statement go? Would that be in the criteria field of the query?

[ADezii]

I think it will work. Where would the IIF statement go? Would that be in the criteria field of the query?

Within the built up SQL statement.

[sesling]

Within the built up SQL statement.

Sorry I am new to Access and I am not clear where the statement would go. Let me provide some more detail to see if this will help us locate the location.

1) I have a form called File Summary. On this form is where the operator can select the file status. Again I am using text boxes to pass this information to a query. Text Box names are [Status1], [Status2], [Status3]
2) The query name is File List - Status. The criteria of this query currently uses the data selected on the form File Summary. Here is the query statement

SELECT DETAIL_COUNT, RECORD_COUNT, STATUS
FROM LOGICAL_FILE
WHERE LOGICAL_FILE.STATUS=[FORMS]![FILE SUMMARY]![STATUS1]OR LOGICAL_FILE.STATUS=[FORMS]![FILE SUMMARY]![STATUS2]
or LOGICAL_FILE.STATUS=[FORMS]![FILE SUMMARY]![STATUS3];

3). The operator will select a Command button on the File Summary form that will open another form that uses the File List - Status query as its row source.

The goal is to change the text boxes to a check boxes. This way the operator would select the check box for the status they want to pass to the query.

[ADezii]

Sorry I am new to Access and I am not clear where the statement would go. Let me provide some more detail to see if this will help us locate the location.

1) I have a form called File Summary. On this form is where the operator can select the file status. Again I am using text boxes to pass this information to a query. Text Box names are [Status1], [Status2], [Status3]
2) The query name is File List - Status. The criteria of this query currently uses the data selected on the form File Summary. Here is the query statement

SELECT DETAIL_COUNT, RECORD_COUNT, STATUS
FROM LOGICAL_FILE
WHERE LOGICAL_FILE.STATUS=[FORMS]![FILE SUMMARY]![STATUS1]OR LOGICAL_FILE.STATUS=[FORMS]![FILE SUMMARY]![STATUS2]
or LOGICAL_FILE.STATUS=[FORMS]![FILE SUMMARY]![STATUS3];

3). The operator will select a Command button on the File Summary form that will open another form that uses the File List - Status query as its row source.

The goal is to change the text boxes to a check boxes. This way the operator would select the check box for the status they want to pass to the query.

Dim strStatus As String
 
If Me![chkOpen] And Me![chkClosed] And Me![chkPending] Then
  strStatus = " IN('Open', 'Closed', 'Pending');"
ElseIf Me![chkOpen] And Me![chkClosed] Then
  strStatus = " IN('Open', 'Closed');"
ElseIf Me![chkPending] And Me![chkClosed] Then
  strStatus = " IN('Pending', 'Closed');"
ElseIf Me![chkOpen] And Me![chkPending] Then
  strStatus = " IN('Open', 'Pending');"
ElseIf Me![chkOpen] Then
  'keep the syntax the same
  strStatus = " IN('Open');"
ElseIf Me![chkClosed] Then
  strStatus = " IN('Closed');"
ElseIf Me![chkPending] Then
  strStatus = " IN('Pending');"
Else    'No Check Box has been selected
  strStatus = " NOT IN('Open', 'Closed', 'Pending');"
End If
 
 "SELECT DETAIL_COUNT, RECORD_COUNT, STATUS
FROM LOGICAL_FILE
WHERE LOGICAL_FILE.STATUS " & strStatus
 

[sesling]

Dim strStatus As String
 
If Me![chkOpen] And Me![chkClosed] And Me![chkPending] Then
  strStatus = " IN('Open', 'Closed', 'Pending');"
ElseIf Me![chkOpen] And Me![chkClosed] Then
  strStatus = " IN('Open', 'Closed');"
ElseIf Me![chkPending] And Me![chkClosed] Then
  strStatus = " IN('Pending', 'Closed');"
ElseIf Me![chkOpen] And Me![chkPending] Then
  strStatus = " IN('Open', 'Pending');"
ElseIf Me![chkOpen] Then
  'keep the syntax the same
  strStatus = " IN('Open');"
ElseIf Me![chkClosed] Then
  strStatus = " IN('Closed');"
ElseIf Me![chkPending] Then
  strStatus = " IN('Pending');"
Else    'No Check Box has been selected
  strStatus = " NOT IN('Open', 'Closed', 'Pending');"
End If
 
 "SELECT DETAIL_COUNT, RECORD_COUNT, STATUS
FROM LOGICAL_FILE
WHERE LOGICAL_FILE.STATUS " & strStatus
 

Got it now. Thx for the help.

[ADezii]

Got it now. Thx for the help.

You're quite welcome.